当前位置: 代码迷 >> Sql Server >> 求SQL,怎么查出连续3天num增长的记录
  详细解决方案

求SQL,怎么查出连续3天num增长的记录

热度:60   发布时间:2016-04-27 20:32:54.0
求SQL,如何查出连续3天num增长的记录
create   table   #t(EM_ID   varchar(6),AttendDate   smalldatetime,num   numeric(10,0))
-----------------------------------  
insert   #a  
select   '001 ',   '2006-01-01 '   ,8.0  
union   all   select   '001 ',   '2006-01-02 '   ,2.0  
union   all   select   '001 ',   '2006-01-03 '   ,3.0  
union   all   select   '001 ',   '2006-01-04 '   ,4.0  
union   all   select   '001 ',   '2006-01-05 ',   5.0  
union   all   select   '001 ',   '2006-01-06 ',   6.0  
union   all   select   '001 ',   '2006-01-07 ',   7.0  
union   all   select   '001 ',   '2006-01-08 ',   8.0  
union   all   select   '002 ',   '2006-01-01 ',   2.0  
union   all   select   '002 ',   '2006-01-02 ',   3.0  
union   all   select   '002 ',   '2006-01-03 ',   1.0  
union   all   select   '002 ',   '2006-01-04 ',   4.0  
union   all   select   '002 ',   '2006-01-05 ',   2.0  
union   all   select   '002 ',   '2006-01-06 ',   6.0  
union   all   select   '003 ',   '2006-01-01 ',   4.0  
union   all   select   '003 ',   '2006-01-03 ',   8.0  
union   all   select   '003 ',   '2006-01-05 ',   16.0  

/*查出num字段连续3天或N天持续增长的记录,中间没有记录的如果持续增长也算*/
001
003


------解决方案--------------------
select EM_ID,count(EM_ID) from t group by EM_ID having count(EM_ID)> 3
------解决方案--------------------
declare @t table(EM_ID varchar(6),AttendDate smalldatetime,num numeric(10,0))
insert into @t
select '001 ', '2006-01-01 ' ,8.0
union all select '001 ', '2006-01-02 ' ,2.0
union all select '001 ', '2006-01-03 ' ,3.0
union all select '001 ', '2006-01-04 ' ,4.0
union all select '001 ', '2006-01-05 ', 5.0
union all select '001 ', '2006-01-06 ', 6.0
union all select '001 ', '2006-01-07 ', 7.0
union all select '001 ', '2006-01-08 ', 8.0
union all select '002 ', '2006-01-01 ', 2.0
union all select '002 ', '2006-01-02 ', 3.0
union all select '002 ', '2006-01-03 ', 1.0
union all select '002 ', '2006-01-04 ', 4.0
union all select '002 ', '2006-01-05 ', 2.0
union all select '002 ', '2006-01-06 ', 6.0
union all select '003 ', '2006-01-01 ', 4.0
union all select '003 ', '2006-01-03 ', 8.0
union all select '003 ', '2006-01-05 ', 16.0

select
distinct t.EM_ID
from
@t t
where
exists(select 1 from @t a where a.EM_ID=t.EM_ID and a.num <t.num and a.AttendDate=(select max(AttendDate) from @t where EM_ID=t.EM_ID and AttendDate <t.AttendDate))
and
exists(select 1 from @t a where a.EM_ID=t.EM_ID and a.num> t.num and a.AttendDate=(select min(AttendDate) from @t where EM_ID=t.EM_ID and AttendDate> t.AttendDate))
  相关解决方案