求sql按时间段分组查询统计的语句
id datetime weizbianh name
1 2012-12-12 00:00:01 1 a
2 2012-12-12 00:00:05 1 b
3 2012-12-12 00:00:10 1 a
4 2012-12-12 00:00:51 1 a
5 2012-12-12 00:00:52 1 b
6 2012-12-12 00:00:53 1 a
7 2012-12-12 00:01:01 1 a
8 2012-12-12 00:01:10 1 a
9 2012-12-12 00:02:01 1 a
想得到结果:
查询出a被b打断了几次!即返回值为: 3
查询a 连续出现的最小记录
即:
id datetime weizbianh name
1 2012-12-12 00:00:01 1 a
3 2012-12-12 00:00:10 1 a
6 2012-12-12 00:00:53 1 a
[解决办法]
USE TEMPDB
GO
IF OBJECT_ID('TB_1') IS NOT NULL
DROP TABLE TB_1
CREATE TABLE TB_1
(
ID INT IDENTITY(1,1),
name VARCHAR(10)
)
INSERT INTOTB_1(name)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a'
GO
SELECT
*
FROM
TB_1 A
WHERE
(
EXISTS(SELECT 1 FROM TB_1 B WHERE B.ID+1 = A.ID AND B.name <> A.name)
OR ID = (SELECT MIN(ID) FROM TB_1)
)
AND name <> 'b'
IF OBJECT_ID('TB_1') IS NOT NULL
DROP TABLE TB_1
GO