--示例数据 create table table5(rq datetime,sl int) insert table5 select '2005-07-31',100 union all select '2005-08-01',100 union all select '2005-08-02',150 union all select '2005-08-03',120 union all select '2005-08-04',100 union all select '2005-08-05',100 union all select '2005-08-06',100 union all select '2005-08-07',100 go --查询处理 select rq as 开始日期,终止日期= (select min(rq) from (select * from table5 where sl=100) a1 where rq>=a.rq and not exists( select * from (select * from table5 where sl=100) x1 where rq=a1.rq+1)) from (select * from table5 where sl=100) a where not exists( select * from (select * from table5 where sl=100) x2 where rq=a.rq-1) --删除测试 drop table table5 /*--查询结果 开始日期 终止日期 ------------ ----------- 2005-07-31 2005-08-01 2005-08-04 2005-08-07 (所影响的行数为 2 行) --*/
create table #temp(a datetime, b int) create table #result(开始日期 datetime, 结束日期 datetime, 连续数量 int) insert #temp select '2005-07-31',100 union all select '2005-08-01',100 union all select '2005-08-02',150 union all select '2005-08-03',120 union all select '2005-08-04',100 union all select '2005-08-05',100 union all select '2005-08-06',100 union all select '2005-08-08',100 union all select '2005-08-09',100 union all select '2005-08-10',100 union all select '2005-08-11',120 union all select '2005-08-12',130 union all select '2005-08-13',100declare @count int, @max_date datetime, @begin_date datetime, @end_date datetimeselect @begin_date = min(a) from #temp where b = 100 select @begin_date = min(a), @max_date = max(a) from #tempif not (@begin_date is null) while (1 = 1) begin select @end_date = min(a) from #temp where (datediff(day, @begin_date, a) > 0) and (b <> 100) if (@end_date is null) break
set @count = datediff(day, @begin_date, @end_date) - 1 if (@count >= 5) insert #result select @begin_date, @end_date - 1, @count select @begin_date = min(a) from #temp where b = 100 and a > @end_date if (@begin_date = @max_date) break endselect * from #resultdrop table #temp, #result
不好意思:上午太急了,忘记条件了 --查询处理select rq,rq1, datediff(day, rq, rq1) +1 from (select rq,rq1= (select min(rq) from (select * from table5 where sl=100) a1 where rq>=a.rq and not exists( select * from (select * from table5 where sl=100) x1 where rq=a1.rq+1)) from (select * from table5 where sl=100) a where not exists( select * from (select * from table5 where sl=100) x2 where rq=a.rq-1) ) xx where datediff(day, rq, rq1)>=4 --连续5条记录
to hthunter(核桃-我的心在下雨,雨中我和她携手漫步): 先感谢您对我问题的细心查看,其实这是我实际问题的精简模型,连续5个或4个倒是关系不大。您给出的方案是用一系列语句的组合来完成的对吗?那么在DELPHI中应当用自定义变量来代替@begin_date之类的变量吧?你的思路我基本上看懂了。谢谢:)to hqhhh(枫叶) ( ):您的方法是用一个单独的完整SQL语句搞定吗?我也很想用一个语句来做,但是真的看不明白你所写的,比如 rql, a1, a.rq, a等我都不明白是从哪来的。对整条语句的层次结构更是雾里看花哦,能给点注释吗?谢谢:)
http://www.somade.com/是个很专业的技术社区,去那里找找吧,或许有你要的答案~
我写的是比较难看懂一些: 1.取出符合条件的记录 select * from table5 where sl=100 如果替换掉,换成临时表就容易看多了 select * into #temp from table5 where sl=1002.取每条记录的结束日期: 格式为: 2005-07-31 2005-08-01 2005-08-01 2005-08-01 2005-08-02 2005-08-02 2005-08-03 2005-08-03 2005-08-04 2005-08-07 2005-08-05 2005-08-07select rq,rq1= (select min(rq) from #temp a1 where rq>=a.rq and not exists( select * from #temp where rq=a1.rq+1)) into #temp2 from #temp where not exists( select * from #temp where rq=a.rq-1) 3.取连续5条记录select rq,rq1, datediff(day, rq, rq1) +1 from (#temp2) xx where datediff(day, rq, rq1)>=4 --连续5条记录
to hqhhh(枫叶) ( ) : 真不好意思,我还是没能看懂你的语句。不过还是很感谢你的关注,希望以后能有机会再请教你:)结帐!
create table table5(rq datetime,sl int)
insert table5 select '2005-07-31',100
union all select '2005-08-01',100
union all select '2005-08-02',150
union all select '2005-08-03',120
union all select '2005-08-04',100
union all select '2005-08-05',100
union all select '2005-08-06',100
union all select '2005-08-07',100
go
--查询处理
select rq as 开始日期,终止日期=
(select min(rq) from (select * from table5 where sl=100) a1
where rq>=a.rq
and not exists(
select * from (select * from table5 where sl=100) x1
where rq=a1.rq+1))
from (select * from table5 where sl=100) a
where not exists(
select * from (select * from table5 where sl=100) x2
where rq=a.rq-1)
--删除测试
drop table table5
/*--查询结果
开始日期 终止日期
------------ -----------
2005-07-31 2005-08-01
2005-08-04 2005-08-07 (所影响的行数为 2 行)
--*/
create table #result(开始日期 datetime, 结束日期 datetime, 连续数量 int)
insert #temp
select '2005-07-31',100
union all select '2005-08-01',100
union all select '2005-08-02',150
union all select '2005-08-03',120
union all select '2005-08-04',100
union all select '2005-08-05',100
union all select '2005-08-06',100
union all select '2005-08-08',100
union all select '2005-08-09',100
union all select '2005-08-10',100
union all select '2005-08-11',120
union all select '2005-08-12',130
union all select '2005-08-13',100declare @count int, @max_date datetime, @begin_date datetime, @end_date datetimeselect @begin_date = min(a) from #temp where b = 100
select @begin_date = min(a), @max_date = max(a) from #tempif not (@begin_date is null)
while (1 = 1)
begin
select @end_date = min(a)
from #temp
where (datediff(day, @begin_date, a) > 0) and (b <> 100) if (@end_date is null) break
set @count = datediff(day, @begin_date, @end_date) - 1
if (@count >= 5) insert #result select @begin_date, @end_date - 1, @count
select @begin_date = min(a) from #temp where b = 100 and a > @end_date
if (@begin_date = @max_date) break
endselect * from #resultdrop table #temp, #result
--查询处理select rq,rq1, datediff(day, rq, rq1) +1 from
(select rq,rq1=
(select min(rq) from (select * from table5 where sl=100) a1
where rq>=a.rq
and not exists(
select * from (select * from table5 where sl=100) x1
where rq=a1.rq+1))
from (select * from table5 where sl=100) a
where not exists(
select * from (select * from table5 where sl=100) x2
where rq=a.rq-1)
) xx where datediff(day, rq, rq1)>=4 --连续5条记录
先感谢您对我问题的细心查看,其实这是我实际问题的精简模型,连续5个或4个倒是关系不大。您给出的方案是用一系列语句的组合来完成的对吗?那么在DELPHI中应当用自定义变量来代替@begin_date之类的变量吧?你的思路我基本上看懂了。谢谢:)to hqhhh(枫叶) ( ):您的方法是用一个单独的完整SQL语句搞定吗?我也很想用一个语句来做,但是真的看不明白你所写的,比如 rql, a1, a.rq, a等我都不明白是从哪来的。对整条语句的层次结构更是雾里看花哦,能给点注释吗?谢谢:)
1.取出符合条件的记录
select * from table5 where sl=100
如果替换掉,换成临时表就容易看多了
select * into #temp from table5 where sl=1002.取每条记录的结束日期:
格式为:
2005-07-31 2005-08-01
2005-08-01 2005-08-01
2005-08-02 2005-08-02
2005-08-03 2005-08-03
2005-08-04 2005-08-07
2005-08-05 2005-08-07select rq,rq1=
(select min(rq) from #temp a1
where rq>=a.rq
and not exists(
select * from #temp
where rq=a1.rq+1))
into #temp2 from #temp
where not exists(
select * from #temp
where rq=a.rq-1) 3.取连续5条记录select rq,rq1, datediff(day, rq, rq1) +1 from
(#temp2) xx where datediff(day, rq, rq1)>=4 --连续5条记录