对于下面表
字段 name AA 日期
a 6 2013-10-13
b 2 2013-10-13
c 3 2013-10-13 a 2 2013-10-12
b -1 2013-10-12
c 1 2013-10-12 a 3 2013-10-11
b 3 2013-10-11
c -3 2013-10-11 a 0 2013-10-10
b 2 2013-10-10
c 3 2013-10-10想要的结果:
1、只查 连续3天 大于0的记录
2、只查 连续2天 大于0的记录
3、只查 连续1天 大于0的记录4、查询 连续2天以上 大于0的记录
谢谢
字段 name AA 日期
a 6 2013-10-13
b 2 2013-10-13
c 3 2013-10-13 a 2 2013-10-12
b -1 2013-10-12
c 1 2013-10-12 a 3 2013-10-11
b 3 2013-10-11
c -3 2013-10-11 a 0 2013-10-10
b 2 2013-10-10
c 3 2013-10-10想要的结果:
1、只查 连续3天 大于0的记录
2、只查 连续2天 大于0的记录
3、只查 连续1天 大于0的记录4、查询 连续2天以上 大于0的记录
谢谢
#A(Name varchar(10),AA int ,[日期]datetime);
insert #A
SELECT 'a',6 ,'2013/10/13'
union all select 'b',2,'2013/10/13'
union all select 'c',3,'2013/10/13'union all select 'a',2,'2013/10/12'
union all select 'b',-1,'2013/10/12'
union all select 'c',1,'2013/10/12'union all select 'a',3,'2013/10/11'
union all select 'b',3,'2013/10/11'
union all select 'c',-3,'2013/10/11'union all select 'a',0,'2013/10/10'
union all select 'b',2,'2013/10/10'
union all select 'c',3,'2013/10/10'select * from #A
;with cte as (
select row_number() over(order by getdate())as rn,* from #A
)select * from cte a
where (select AA from cte b where b.rn =a.rn -1 )>0 and (select AA from cte c where c.rn =a.rn -2 )>0 and a.AA>0drop table #A
WITH a1 ([name],[AA],[日期]) AS
(
SELECT 'a',6,'2013-10-13' UNION ALL
SELECT 'b',2,'2013-10-13' UNION ALL
SELECT 'c',3,'2013-10-13' UNION ALL
SELECT 'a',2,'2013-10-12' UNION ALL
SELECT 'b',-1,'2013-10-12' UNION ALL
SELECT 'c',1,'2013-10-12' UNION ALL
SELECT 'a',3,'2013-10-11' UNION ALL
SELECT 'b',3,'2013-10-11' UNION ALL
SELECT 'c',-3,'2013-10-11' UNION ALL
SELECT 'a',0,'2013-10-10' UNION ALL
SELECT 'b',2,'2013-10-10' UNION ALL
SELECT 'c',3,'2013-10-10'
)
,a2 AS
(
SELECT *,DATEADD(dd,-(ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [日期])),[日期]) date2
FROM a1
WHERE [AA]>0
)
,a3 AS
(
SELECT [name],date2
FROM a2
GROUP BY [name],date2
HAVING COUNT(*)=3--1、只查 连续3天 大于0的记录
--HAVING COUNT(*)=2--2、只查 连续2天 大于0的记录
--HAVING COUNT(*)=1--3、只查 连续1天 大于0的记录
--HAVING COUNT(*)>=2--4、查询 连续2天以上 大于0的记录
)
SELECT a2.[name],a2.[AA],a2.[日期]
FROM a2
INNER JOIN a3 ON a2.[name]=a3.[name] AND a2.date2=a3.date2
ORDER BY a2.[name],a2.[日期]
所以连续几天的针对同一个人而言,不知道说清楚没有,呵呵
#A(Name varchar(10),AA int ,[日期]datetime);
insert #A
SELECT 'a',6 ,'2013/10/13'
union all select 'b',2,'2013/10/13'
union all select 'c',3,'2013/10/13'union all select 'a',2,'2013/10/12'
union all select 'b',-1,'2013/10/12'
union all select 'c',1,'2013/10/12'union all select 'a',3,'2013/10/11'
union all select 'b',3,'2013/10/11'
union all select 'c',-3,'2013/10/11'union all select 'a',0,'2013/10/10'
union all select 'b',2,'2013/10/10'
union all select 'c',3,'2013/10/10'select * from #A;with cte as (
select row_number() over(partition by name order by [日期])as rn,* from #A
)select a.* from cte a
cross apply ( select* from cte b
where a.Name =b.Name and a.rn =b.rn
and (select AA from cte d where d.rn =b.rn -1 and d.Name =b.Name )>0
and (select AA from cte e where e.rn =b.rn -2 and e.Name =b.Name )>0
and b.AA >0
)abdrop table #A