求大侠帮忙一条sql语句
我一个张表 结构如下
IDTIMESTATUS;
110:30A
210:32B
310:33B
410:35B
510:37C
610:50C
711:32B
812:32B
912:52A
1012:59B
1113:09A
1213:19A
1313:29D
1414:29D
1515:29B
1616:29B
1717:29B
1818:29C
1919:29C
我想要到如下结果：
IDSTATTIMESTOPTIME STATUS
110:3210:35B
211:3212:32B
315:2917:29B
[解决办法]
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
