双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

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

2012-10-08 02:36:45 435 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

wj081066 墨衍会员 · AI 创作全网分发关注 + 加入社区

python统计jira缺陷数据环境：python2.7 1、安装jira库 pip install jira 2、python连接jira服务器 #coding: UTF-8 from jira import JIRA username = raw_input('请输入用户名: ') password = raw_input('输入密码: ') options = { 'verify': Fa... 阅读详情

请教各位一个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
.. ... ...... ...... ...

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

...全文

树莓派4B+Ubuntu22.04+思岚RPLIDAR C1激光雷达应用树莓派+Ubuntu22.04+思岚RPLIDAR C1激光雷达简单应用阅读详情

wj081066

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

汤姆克鲁斯 2012-10-08

打赏
举报

--> 测试数据：[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

*/

ttpsan550 2012-10-08

打赏
举报

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

ttpsan550 2012-10-08

打赏
举报



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 行受影响)

✦双11狂欢大促✦

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

相关推荐

双11狂欢大促