求助Sql语句，统计连续没有缺陷的次数大于2次的(有数据)

[wj081066]

请教各位一个sql语句(时间已经排好序了，统计这个人连续没有出现缺陷的次数，超过两次的。)：

人员ID 姓名 生产日期 是否有缺陷
1 张三 2012-08-28 12:11:23
1 张三 2012-09-02 08:34:12 Y
1 张三 2012-09-04 10:12:13
1 张三 2012-09-05 23:12:17
1 张三 2012-09-08 17:11:48
1 张三 2012-09-10 01:55:35
1 张三 2012-09-12 18:34:23 Y
1 张三 2012-09-13 13:45:18
1 张三 2012-09-18 19:22:19 Y
1 张三 2012-09-19 15:38:55
1 张三 2012-09-22 18:24:21
1 张三 2012-09-25 17:15:45
2 李四 2012-08-27 15:21:46
2 李四 2012-09-04 11:34:13 Y
2 李四 2012-09-14 17:55:44
2 李四 2012-09-18 08:09:28
2 李四 2012-09-19 18:05:55
2 李四 2012-09-25 12:10:45
3 王五 2012-08-27 16:45:23
3 王五 2012-09-10 15:47:13
3 王五 2012-09-17 13:34:07 Y
3 王五 2012-09-18 14:36:19
3 王五 2012-09-25 19:23:40
... ... ...... ...

得到以下结果：
人员ID 姓名 连续没有缺陷开始日期 连续没有缺陷结束日期 次数(>2次)
1 张三 2012-09-04 10:12:13 2012-09-10 01:55:35 4
1 张三 2012-09-19 15:38:55 2012-09-25 17:15:45 3
2 李四 2012-09-14 17:55:44 2012-09-25 12:10:45 4
.. ... ...... ...... ...

时间已经排好序了，统计这个人连续没有出现缺陷的次数，超过两次的。多谢各位~~

[ttpsan550]

if OBJECT_ID('tb') is not null drop table tb
go
create table tb
(
人员ID int,
姓名 nvarchar(10),
生产日期 datetime,
是否有缺陷 char(2)
)
insert into tb
select
1,'张三',	'2012-08-28 12:11:23',''
union all select
1,	'张三',	'2012-09-02 08:34:12','Y'
union all	select
1,	'张三',	'2012-09-04 10:12:13','' union all		select
1,	'张三',	'2012-09-05 23:12:17',''union all	select
1,	'张三',	'2012-09-08 17:11:48',''union all	select
1,	'张三',	'2012-09-10 01:55:35',''union all	select
1,	'张三',	'2012-09-12 18:34:23','Y'union all	select
1,	'张三',	'2012-09-13 13:45:18',''	union all	select
1,	'张三',	'2012-09-18 19:22:19'	,'Y'union all	select
1,	'张三',	'2012-09-19 15:38:55',''	union all	select
1,	'张三',	'2012-09-22 18:24:21'	,''union all	select
1,	'张三',	'2012-09-25 17:15:45'	,''union all	select
2,	'李四',	'2012-08-27 15:21:46'	,''union all	select
2,	'李四',	'2012-09-04 11:34:13'	,'Y'union all	select
2,	'李四',	'2012-09-14 17:55:44'	,''union all	select
2,	'李四',	'2012-09-18 08:09:28'	,''union all	select
2,	'李四',	'2012-09-19 18:05:55'	,''union all	select
2,	'李四',	'2012-09-25 12:10:45'	,''union all	select
3,	'王五',	'2012-08-27 16:45:23'	,''union all	select
3,	'王五',	'2012-09-10 15:47:13'	,''union all	select
3,	'王五',	'2012-09-17 13:34:07'	,'Y'union all	select
3,	'王五',	'2012-09-18 14:36:19',''union all	select
3,	'王五',	'2012-09-25 19:23:40'	,''
;
with tb1 as
(select ROW_NUMBER ()over(PARTITION by 人员ID order by 生产日期) as id , 人员ID ,
姓名 ,
生产日期 ,
是否有缺陷 from tb)
, TB2 AS
(
select *
,id-ISNULL((select top 1 id from tb1 a where a.人员ID =tb1.人员ID and a.id <=tb1.id and a.是否有缺陷='Y' ORDER BY A.id DESC),0)JG
 ,ISNULL((select top 1 id from tb1 a where a.人员ID =tb1.人员ID and a.id <=tb1.id and a.是否有缺陷='Y' ORDER BY A.id DESC),0) AS IDS
 from tb1 
)
SELECT 人员ID,姓名,
(SELECT MIN(生产日期) FROM TB2 B WHERE   TB2.人员ID=B.人员ID AND TB2.IDS=B.IDS and ISNULL(B.是否有缺陷,'') <>'Y') as 开始时间,
生产日期 as 结束时间,JG AS 次数
 FROM tb2 WHERE JG>2 AND NOT EXISTS(SELECT 1 FROM TB2 B WHERE B.JG>TB2.JG AND TB2.人员ID=B.人员ID AND TB2.IDS=B.IDS) 
DROP TABLE TB
=================================

人员ID        姓名         开始时间                    结束时间                    次数
----------- ---------- ----------------------- ----------------------- --------------------
1           张三         2012-09-04 10:12:13.000 2012-09-10 01:55:35.000 4
1           张三         2012-09-19 15:38:55.000 2012-09-25 17:15:45.000 3
2           李四         2012-09-14 17:55:44.000 2012-09-25 12:10:45.000 4

(3 行受影响)

[ttpsan550]

这句 还要加个 排序 select ROW_NUMBER ()over(PARTITION by 人员ID order by 人员ID,生产日期

[汤姆克鲁斯]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([人员ID] INT,[姓名] VARCHAR(4),[生产日期] DATETIME,[是否有缺陷] VARCHAR(1))
INSERT [tb]
SELECT 1,'张三','2012-08-28 12:11:23',NULL UNION ALL
SELECT 1,'张三','2012-09-02 08:34:12','Y' UNION ALL
SELECT 1,'张三','2012-09-04 10:12:13',NULL UNION ALL
SELECT 1,'张三','2012-09-05 23:12:17',NULL UNION ALL
SELECT 1,'张三','2012-09-08 17:11:48',NULL UNION ALL
SELECT 1,'张三','2012-09-10 01:55:35',NULL UNION ALL
SELECT 1,'张三','2012-09-12 18:34:23','Y' UNION ALL
SELECT 1,'张三','2012-09-13 13:45:18',NULL UNION ALL
SELECT 1,'张三','2012-09-18 19:22:19','Y' UNION ALL
SELECT 1,'张三','2012-09-19 15:38:55',NULL UNION ALL
SELECT 1,'张三','2012-09-22 18:24:21',NULL UNION ALL
SELECT 1,'张三','2012-09-25 17:15:45',NULL UNION ALL
SELECT 2,'李四','2012-08-27 15:21:46',NULL UNION ALL
SELECT 2,'李四','2012-09-04 11:34:13','Y' UNION ALL
SELECT 2,'李四','2012-09-14 17:55:44',NULL UNION ALL
SELECT 2,'李四','2012-09-18 08:09:28',NULL UNION ALL
SELECT 2,'李四','2012-09-19 18:05:55',NULL UNION ALL
SELECT 2,'李四','2012-09-25 12:10:45',NULL UNION ALL
SELECT 3,'王五','2012-08-27 16:45:23',NULL UNION ALL
SELECT 3,'王五','2012-09-10 15:47:13',NULL UNION ALL
SELECT 3,'王五','2012-09-17 13:34:07','Y' UNION ALL
SELECT 3,'王五','2012-09-18 14:36:19',NULL UNION ALL
SELECT 3,'王五','2012-09-25 19:23:40',NULL
--------------开始查询--------------------------
;WITH cte AS(
SELECT *,row_id=ROW_NUMBER() OVER(PARTITION BY [人员ID] ORDER BY [生产日期]) FROM [tb]
)
, cte2 AS(
SELECT  [人员ID],[姓名],[生产日期],[row_id]
FROM    cte AS c
WHERE   EXISTS ( SELECT 1
                 FROM   cte
                 WHERE  [人员ID] = c.[人员ID]
                        AND (
                             [row_id] = c.[row_id]-1
                             OR 
                             [row_id] = c.[row_id]+1
                            )
                        AND ISNULL([是否有缺陷] , '') != 'Y' )
        AND ISNULL([是否有缺陷] , '') != 'Y'
)
,cte3 AS(
SELECT  * , [end_id]=(
             SELECT min([row_id])
             FROM   cte2 AS a
             WHERE  [row_id] > c.[row_id]
                    AND NOT EXISTS ( SELECT 1
                                     FROM   cte2
                                     WHERE  [人员ID] = c.[人员ID]
                                            AND [row_id] = a.[row_id]+1 
                                           )
            )
            ,[end_date]=(
             SELECT min([生产日期])
             FROM   cte2 AS a
             WHERE  [row_id] > c.[row_id]
                    AND NOT EXISTS ( SELECT 1
                                     FROM   cte2
                                     WHERE  [人员ID] = c.[人员ID]
                                            AND [row_id] = a.[row_id]+1 
                                           )  
              )
FROM    cte2 AS c
WHERE   NOT EXISTS ( SELECT 1
                     FROM   cte2
                     WHERE  [人员ID] = c.[人员ID]
                            AND [row_id] = c.[row_id]-1 )
)                          
SELECT [人员ID],[姓名],连续没有缺陷开始日期=[生产日期],连续没有缺陷结束日期=[end_date],次数=[end_id]-[row_id]+1
FROM cte3 WHERE [end_id]-[row_id]>1
----------------结果----------------------------
/*
人员ID	姓名	连续没有缺陷开始日期	连续没有缺陷结束日期	次数
1	张三	2012-09-04 10:12:13.000	2012-09-10 01:55:35.000	4
1	张三	2012-09-19 15:38:55.000	2012-09-25 17:15:45.000	3
2	李四	2012-09-14 17:55:44.000	2012-09-10 01:55:35.000	4
*/