MySQL 查询，用于在两行中获取相同的列数据

Question

我是MySQL的新手。我有一个数据库，我在获取以下格式的数据时遇到问题。

表格如下

+----+------+-------+-+
| ID | ign  | time  | |
+----+------+-------+-+
|  1 | NULL | 12:30 | |
|  2 |    1 | 12:31 | |
|  3 | NULL | 12:32 | |
|  4 | NULL | 12:33 | |
|  5 | NULL | 12:34 | |
|  6 |    0 | 12:35 | |
|  7 | NULL | 12:36 | |
|  8 | NULL | 12:37 | |
|  9 |    1 | 12:38 | |
| 10 | NULL | 12:39 | |
| 11 | NULL | 12:40 | |
| 12 | NULL | 12:41 | |
| 13 |    0 | 12:42 | |
| 14 | NULL | 12:43 | |
| 15 | NULL | 12:44 | |
+----+------+-------+-+

我的结果表应采用以下格式。

Start | Start Time | Stop |Stop Time
1     | 12:31      | 0    | 12:35
1     | 12:38      | 0    | 12:42

结果是通过交替的 1 和 0 放在一起获得的。值为 1 的第一个 ign 是开始，值为 0 的第二个 ign 是停止。相应 1 和 0 的时间值是开始和停止时间。我是MySQL的新手，不知道如何实现这一点。

Answer 1

您可以使用相关的子查询来执行此操作：

select t.ign `Start`, t.time `Start Time`, 0 `Stop`,
(select min(time) from tablename where ign = 0 and time > t.time) `Stop Time`
from tablename t 
where t.ign = 1

对于ign = 1的每一行，子查询返回ign = 0的最小稍后时间。
请参阅演示。
结果：

| Start | Start Time | Stop | Stop Time |
| ----- | ---------- | ---- | --------- |
| 1     | 12:31      | 0    | 12:35     |
| 1     | 12:38      | 0    | 12:42     |

Answer 2

这是一种间隙和孤岛问题。您可以执行窗口总和来定义组：忽略nullign，每次满足1时都会启动一个新组。

对于数据集，以下查询将生成预期结果：

select 1 start, min(time) startTime, 0 stop, max(time) stopTime
from (
select t.*, sum(ign) over(order by time) grp
from mytable t
where ign is not null
) t
group by grp

请注意，窗口函数是在MySQL 8.0中添加的。

DB小提琴上的演示：

开始 | 开始时间 | 停止

| 停止时间 ----: |:-------- |---: |:------- 1 |12：31 |   0 |12:35   1 |12：38 |   0 |12：42