我一个张表 结构如下
ID TIME STATUS;
1 10:30 A
2 10:32 B
3 10:33 B
4 10:35 B
5 10:37 C
6 10:50 C
7 11:32 B
8 12:32 B
9 12:52 A
10 12:59 B
11 13:09 A
12 13:19 A
13 13:29 D
14 14:29 D
15 15:29 B
16 16:29 B
17 17:29 B
18 18:29 C
19 19:29 C
我想要到如下结果：
ID STATTIME STOPTIME STATUS
1 10:32 10:35 B
2 11:32 12:32 B
3 15:29 17:29 B
------解决方案--------------------------------------------------------
SELECT STATUS,MAX(TIME),MIN(TIME) FROM (
SELECT *,COALESCE(newid,(SELECT MAX(id) FROM ttw)) AS ID1
FROM (
SELECT *,(SELECT MIN(id) FROM ttw WHERE a.id<id AND a.STATUS<>STATUS)-1 AS newid FROM ttw a)A1)A2
WHERE
STATUS='B'
GROUP BY STATUS ,ID1 HAVING MAX(ID1)-MIN(ID)>=1