如何用SQL获得每个组的一个结果?

I really don't know how to put a query for this task.

我真的不知道如何对这个任务进行查询。

I need to get a time for the first row of each record in the table but every time when I have a group of same results I need to get the first one when it occurs and when signal is equal to 1.

我需要得到表中每条记录的第一行的时间但是每次当我有一组相同的结果时我需要得到第一行当它发生时当信号等于1时。

I don't know how to explain it correctly but I will make an example and you'll see what I mean.

我不知道如何正确地解释它，但我会举一个例子，你们会明白我的意思。

Records:

记录:

ID| Time | Serial | Signal
 1| 10:59|   12   |   1
 2| 11:00|   12   |   1
 3| 11:01|   12   |   1
 4| 11:01|   13   |   1
 5| 11:02|   12   |   0
 6| 11:03|   12   |   0
 7| 11:05|   13   |   1
 8| 11:07|   12   |   1

So I need this result:

所以我需要这个结果:

ID| Time | Serial | Signal
 1| 10:59|   12   |   1
 4| 11:01|   13   |   1
 7| 11:05|   13   |   1
 8| 11:07|   12   |   1

4 个解决方案

#1

Your exact logic is not clear, from what I can gather you are looking to get each row where signal is 1 where the serial value changes from one time to another.

你的确切逻辑是不清楚的，从我能收集到的数据来看，你在寻找信号为1的每一行，序列值从一次到另一次的变化。

This can be done using a variable to track the value of the serial in the previous row (variable @t in my query below).

这可以使用一个变量来跟踪前一行中的序列号(在下面的查询中是变量@t)。

SELECT  ID, Time, `Serial`, `Signal`
FROM    (   SELECT  ID,
                    Time,
                    `Serial`,
                    `Signal`,
                    CASE WHEN `Serial` = @t THEN 0 ELSE 1 END AS IsNew,
                    @t:= `Serial`
            FROM    T,
                    (SELECT @t:=0) t2
            ORDER BY Time
        ) t
WHERE   IsNew = 1
AND     `Signal` = 1

SQL Fiddle

SQL小提琴

#2

You can do this way by generating a rank for consecutive Serial values and then by selecting only records having first rank. Try this query:

你可以通过为连续的序列值生成一个等级，然后选择只有一级的记录来实现。试试这个查询:

SELECT id, atime, serial, asignal
FROM (
      SELECT id,
             atime,
             asignal,
             IF(a.serial = @var_serial, (@var_rank:= @var_rank + 1),
                                        @var_rank := 1) AS rank,
             (@var_serial := a.serial) AS Serial
      FROM test_table a, (SELECT @var_rank := 1, @var_serial := 0) r
      ORDER BY id ASC
     )a
WHERE asignal = 1
      AND rank = 1;

Example: SQLFiddle

#3

I would start trying something like

我会开始尝试

SELECT * FROM tablename where Signal=1  GROUP BY Time ORDER BY Time ASC

#4

SELECT * FROM your_table
WHERE id IN (
  SELECT MIN(id) FROM your_table
  WHERE Signal != 0
  GROUP BY Serial
)