求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))
/*
EM_ID
------
001
003
*/
[解决办法]
问题的关键是如何判断日期连续
看看这个贴子，说不定对你有帮助
http://topic.csdn.net/t/20050110/14/3713252.html
感觉有点类似字符串匹配搜索
[解决办法]
)_(
[解决办法]
给你个思路你自己去想吧
drop table #tt1
go
create table #tt1 (em_id varchar(6),
attenddate datetime,
num money,
lastdate datetime null,
increaseflag int null,
em_line_id int null)
go
insert into #tt1
select '001 ', '2006-01-01 ' ,8.0 ,null,null,null
union all select '001 ', '2006-01-02 ' ,2.0 ,null,null,null
union all select '001 ', '2006-01-03 ' ,3.0 ,null,null,null
union all select '001 ', '2006-01-04 ' ,4.0 ,null,null,null
union all select '001 ', '2006-01-05 ', 5.0 ,null,null,null
union all select '001 ', '2006-01-06 ', 6.0 ,null,null,null
union all select '001 ', '2006-01-07 ', 7.0 ,null,null,null
union all select '001 ', '2006-01-08 ', 8.0 ,null,null,null
union all select '002 ', '2006-01-01 ', 2.0 ,null,null,null
union all select '002 ', '2006-01-02 ', 3.0 ,null,null,null
union all select '002 ', '2006-01-03 ', 1.0 ,null,null,null
union all select '002 ', '2006-01-04 ', 4.0 ,null,null,null
union all select '002 ', '2006-01-05 ', 2.0 ,null,null,null
union all select '002 ', '2006-01-06 ', 6.0 ,null,null,null
union all select '003 ', '2006-01-01 ', 4.0 ,null,null,null
union all select '003 ', '2006-01-03 ', 8.0 ,null,null,null
union all select '003 ', '2006-01-05 ', 16.0 ,null,null,null
go
update #tt1 set
lastdate = (select max(attenddate) from #tt1 z
where #tt1.em_id = z.em_id
and #tt1.attenddate > z.attenddate),
em_line_id = (select count(1) from #tt1 z
where #tt1.em_id = z.em_id
and #tt1.attenddate > = z.attenddate)
go
--
update #tt1 set
increaseflag=1
where lastdate is null
go
--
update #tt1 set
increaseflag=sign(#tt1.num-(select max(num) from #tt1 z
where #tt1.em_id = z.em_id
and #tt1.lastdate= z.attenddate))
where increaseflag is null
go
select * from #tt1 a
where not exists(select 1 from #tt1 z
where a.em_line_id - z.em_line_id <=3
and a.em_id =z.em_id
and increaseflag <> 1)
go
select distinct em_id from #tt1 a
where not exists(select 1 from #tt1 z
where a.em_line_id - z.em_line_id <=3
and a.em_id =z.em_id
and increaseflag <> 1)
