22,302
社区成员




--> 测试数据:[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
*/
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 行受影响)