日期:2014-05-18 浏览次数:20595 次
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [ID] int, [用户名] varchar(4), [积分] int, [统计日期] datetime ) go insert [test] select 1,'张三',100,'2012-01-02' union all select 2,'李四',66,'2012-01-03' union all select 3,'张三',77,'2012-01-03' union all select 4,'李四',88,'2012-01-05' go with t as( select px=DATEADD(DD,-ROW_NUMBER()over(partition by [用户名] order by [统计日期] asc),[统计日期]), [用户名],[统计日期] from test where [积分]<>0 or [积分]<>'' ), m as( select [用户名],MIN([统计日期]) as StartTime,MAX([统计日期]) as EndTime from t group by px,[用户名] ) select [用户名],DATEDIFF(DD,StartTime,EndTime)+1 as 连续天数 from m where DATEDIFF(DD,StartTime,EndTime)>=1 /* 用户名 连续天数 ------------------ 张三 2 */
------解决方案--------------------
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [ID] int, [用户名] varchar(4), [积分] int, [统计日期] datetime ) go insert [test] select 1,'张三',100,'2012-01-02' union all select 2,'李四',66,'2012-01-03' union all select 3,'张三',77,'2012-01-03' union all select 4,'李四',88,'2012-01-05' go with t as( select px=DATEADD(DD,-ROW_NUMBER()over(partition by [用户名] order by [统计日期] asc),[统计日期]), [用户名],[统计日期] from test where [积分]>=60 ), m as( select [用户名],MIN([统计日期]) as StartTime,MAX([统计日期]) as EndTime from t group by px,[用户名] ) select [用户名],DATEDIFF(DD,StartTime,EndTime)+1 as 连续天数 from m where DATEDIFF(DD,StartTime,EndTime)>=1 /* 用户名 连续天数 ------------------ 张三 2 */ --你把最后那个DATEDIFF(DD,StartTime,EndTime)>=1改成DATEDIFF(DD,StartTime,EndTime)>=6就好了
------解决方案--------------------
SELECT A.[Name] ,DateDiff(Day,Min(B.Date),Max(A.Date))+1 as '连续日期' FROM [TestDB].[dbo].[Score] A Inner join [TestDB].[dbo].[Score] B ON A.Date=B.Date+1 And A.Name=B.Name Group By A.[Name]
------解决方案--------------------
--确实范围和现有范围(也称间断和孤岛问题) --1、缺失范围(间断) /* 整理人:TravyLee 时间:2012-03-25 如有引用,请标明“此内容源自MSSQL2008技术内幕之T-SQL” */ /* 求解间断问题有几种方法,小弟我选择性能较高的三种(使用游标 的方法省略) ------------------------------------------------------- -------------- 间断问题的解决方案1;使用子查询 step 1:找到间断之前的值,每个值增加一个间隔 step 2:对于没一个间断的起点,找出序列中现有得值,再减去一 个间隔 本人理解为:找到原数据表中的值加一减一是否存在,若有不妥, 望纠正 生成测试数据: go if object_id('tbl')is not null drop table tbl go create table tbl( id int not null ) go insert tbl values(2),(3),(11),(12),(13),(27),(33),(34),(35),(42) 要求:找到上表数据中的不存在的id的范围, --实现输出结果: /* 开始范围 结束范围 4 10 14 26 28 32 36 41 */ 按照每个步骤实现: step 1:找到间断之前的值,每个值增加一个间隔 我们可以清楚的发现,要找的间断范围的起始值实际上就是我们 现有数据中的某些值加1后存不存在现有数据表中的问题,通过 子查询实现: select id+1 as start_range from tbl as a where not exists(select 1 from tbl as b where b.id=a.id+1)and id<(select max(id) from tbl) --此查询语句实现以下输出: /* start_range 4 14 28 36 */ step 2:对于没一个间断的起点,找出序列中现有得值,再减去 一个间隔 select id+1 as start_range,(select min(b.id) from tbl as b where b.id>a.id)-1 as end_range from tbl a where not exists(select 1 from tbl as b where b.id=a.id+1) and id<(select max(id) from tbl) --输出结果: /* start_range end_range 4 10 14 26 28 32 36 41 */ 通过以上的相关子查询我们实现了找到原数据表中的间断范围。 而且这种方式的效率较其他方式有绝对的优势 间断问题的解决方案2;使用子查询(主意观察同1的区别) step 1:对每个现有的值匹配下一个现有的值,生成一对一对的当 前值和下一个值 step 2:只保留下一个值减当前值大于1的间隔值对 step 3:对剩下的值对,将每个当前值增加1个间隔,将每个下一 个值减去一个间隔 --转换成T-SQL语句实现: --step 1: select id as cur,(select min(b.id) from tbl b where b.id>a.id) as nxt from tbl a --此子查询生成的结果: /*