-
本版专家分：20结帖率 93.75% -
请教各位一个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
.. ... ...... ...... ...
时间已经排好序了，统计这个人连续没有出现缺陷的次数，超过两次的。多谢各位~~
-
本版专家分：760 -
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 行受影响)
-
本版专家分：760
-
这句 还要加个 排序 select ROW_NUMBER ()over(PARTITION by 人员ID order by 人员ID,生产日期
-
本版专家分：15595
-
- 银牌 2012年10月 总版技术专家分月排行榜第二
-
- 红花 2012年10月 MS-SQL Server大版内专家分月排行榜第一
-
- 黄花 2012年9月 MS-SQL Server大版内专家分月排行榜第二
-
-
--> 测试数据：[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
*/
- sql 语句 按照某年某月某周查询每位学生或员工的作业提交次数或打卡记录数据统计
sql 语句 按照某年某月某周查询每位学生或员工的作业提交次数或打卡记录数据统计。 对考勤次数，提交信息考核统计很重要。
- 7天带你玩转Mysql数据库之SQL语句
2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。 3. 稍微复杂⼀点的SQL就不知道该如何写了。 4. 看不懂同⾏写的SQL是为什么。 5. 不知道索引的原理。 6. 不...
- SQL语句大全经典教程
教程名称：SQL语句大全经典教程课程目录：【】118个sql常用功能语句汇总【技术文档】【】MySQL从入门到精通【】SQL 基础语句【】SQL经典语句和实例【整理资料】【】SQL语句大全【经典教程】【】SQL语句大全之循序渐...
- sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全
全面的sql语句sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全
- 数据库执行SQL语句跟踪
不管你是刚学习软件开发的初学者，还是在IT职场打拼多年的职场老鸟，都免不了要同各种数据库打交道，你可能会碰到以下几个问题：1、感觉自己写的sql语句没有错，但是执行结果总是不对；2、某个功能执行的语句知道有...
- 统计成绩SQL语句
计算每个人的总成绩并排名 select CourseID,sum(Score) as allscore from ScoreInformation group by CourseID order by allscore
- 常用复杂sql语句
常用复杂sql语句.txt 常用复杂sql语句.txt 常用复杂sql语句.txt
- Oracle Sql语句转换成Mysql Sql语句
Oracle Sql语句转换成Mysql Sql语句java 源码，非常简单，只要给定源oracle sql语句地址，和生成目标文件地址运行即可。
- 实验2 数据定义与查询 SQL语句
1、掌握使用SQL语句创建和删除数据表，创建各种完整性约束。 2、掌握使用SQL语句修改表的结构。 3、掌握查询语句的使用方法，重点掌握连接查询和嵌套查询。 1．创建名为“Project”的数据库 2．在Project数据库下...
- 50个常用sql语句常用的sql语句
50个常用sql语句常用的sql语句，比较使用，适合共学习的朋友用。。
- SQL语句辅助工具
SQL语句辅助工具，将SQL语句生成C#语句
- 常用 SQL 语句大全+50个常用sql语句总结
常用SQL 语句大全+50个常用sql语句总结
- 数据库表数据转为insert sql语句
1、对ms sql server及其他数据库库中类似int identity的数据库类型没有处理,转出来的sql语句直接执行时,还需要做一下处理. 2、不支持长字符集类型.比如ms sql server中的image,oracle中的blob,clob,long以及db2中...
- 易语言常用SQL语句
易语言常用SQL语句，易语言常用SQL语句，易语言常用SQL语句，易语言常用SQL语句，
- 常用SQL语句大全
软件介绍 常用SQL语句大全 语 句 功 能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 ...Tags： 常用SQL语句大全 SQL语句大全 SQL
- SQL语句测试工具
SQL语句测试工具
- SQL语句视频课程(进阶版)
本课程系SQL语句从入门到精通迅速提升篇视频课程（oracle零基础版）的进阶版，主要讲解索引、视图、物化视图、触发器、存储过程等常见数据库对象进行讲解。 使大家对常见数据库对象有较为清晰的认识，同时能够...
- 输入sql语句导出excel
输入sql语句导出excel 代码参考
- Java打印漂亮的SQL语句(被格式化的SQL语句)
在项目开发的过程中难免需要打印一下自己拼写的SQL语句，尤其是很长的SQL语句，看起来很不舒服，使用该jar包，打印出来的SQL语句已经被格式化，结构很清晰、简洁，更方便我们调试及分析
- Oracle SQL语句跟踪
Oracle SQL语句跟踪
我们是很有底线的