数据表如下:
date_ hour_ cs_tag
2008-6-14 00:00 9 46812
2008-6-14 00:00 10 46812
2008-6-19 00:00 13 46812
2008-6-19 00:00 14 46812
2008-6-19 00:00 15 46812
2008-6-19 00:00 16 46812
2008-6-19 00:00 17 46812
2008-6-19 00:00 18 46812
2008-6-19 00:00 19 46812
2008-6-19 00:00 20 46812
2008-6-19 00:00 21 46812
2008-6-19 00:00 22 46812
2008-6-20 00:00 8 46812
2008-6-20 00:00 9 46812
2008-6-1 00:00 8 50715
2008-6-1 00:00 9 50715
2008-6-1 00:00 10 50715
2008-6-1 00:00 11 50715
2008-6-1 00:00 12 50715 要求查询所有连续的数据结果如下:
date_ cs_tag s_hour e_hour
2008-6-14 00:00 46812 9 10
2008-6-19 00:00 46812 13 22
2008-6-20 00:00 46812 8 9
2008-6-1 00:00 50715 8 12不知道我说明白了没有,谢谢大家了!!
date_ hour_ cs_tag
2008-6-14 00:00 9 46812
2008-6-14 00:00 10 46812
2008-6-19 00:00 13 46812
2008-6-19 00:00 14 46812
2008-6-19 00:00 15 46812
2008-6-19 00:00 16 46812
2008-6-19 00:00 17 46812
2008-6-19 00:00 18 46812
2008-6-19 00:00 19 46812
2008-6-19 00:00 20 46812
2008-6-19 00:00 21 46812
2008-6-19 00:00 22 46812
2008-6-20 00:00 8 46812
2008-6-20 00:00 9 46812
2008-6-1 00:00 8 50715
2008-6-1 00:00 9 50715
2008-6-1 00:00 10 50715
2008-6-1 00:00 11 50715
2008-6-1 00:00 12 50715 要求查询所有连续的数据结果如下:
date_ cs_tag s_hour e_hour
2008-6-14 00:00 46812 9 10
2008-6-19 00:00 46812 13 22
2008-6-20 00:00 46812 8 9
2008-6-1 00:00 50715 8 12不知道我说明白了没有,谢谢大家了!!
date_ hour_ cs_tag
2008-6-14 00:00 9 46812
2008-6-14 00:00 10 46812
2008-6-19 00:00 13 46812
2008-6-19 00:00 14 46812
2008-6-19 00:00 15 46812
2008-6-19 00:00 16 46812
2008-6-19 00:00 17 46812
2008-6-19 00:00 18 46812
2008-6-19 00:00 19 46812
2008-6-19 00:00 20 46812
2008-6-19 00:00 21 46812
2008-6-19 00:00 22 46812
2008-6-20 00:00 8 46812
2008-6-20 00:00 9 46812
2008-6-1 00:00 8 50715
2008-6-1 00:00 9 50715
2008-6-1 00:00 10 50715
2008-6-1 00:00 11 50715
2008-6-1 00:00 12 50715 要求查询所有连续的数据结果如下:
date_ cs_tag s_hour e_hour
2008-6-14 00:00 46812 9 10
2008-6-19 00:00 46812 13 22
2008-6-20 00:00 46812 8 9
2008-6-1 00:00 50715 8 12 不知道我说明白了没有,谢谢大家了!!
Insert @T
select '2008-6-14 00:00',9,46812 union all
select '2008-6-14 00:00',10,46812 union all
select '2008-6-19 00:00',13,46812 union all
select '2008-6-19 00:00',14,46812 union all
select '2008-6-19 00:00',15,46812 union all
select '2008-6-19 00:00',16,46812 union all
select '2008-6-19 00:00',17,46812 union all
select '2008-6-19 00:00',18,46812 union all
select '2008-6-19 00:00',19,46812 union all
select '2008-6-19 00:00',20,46812 union all
select '2008-6-19 00:00',21,46812 union all
select '2008-6-19 00:00',22,46812 union all
select '2008-6-20 00:00',8,46812 union all
select '2008-6-20 00:00',9,46812 union all
select '2008-6-1 00:00',8,50715 union all
select '2008-6-1 00:00',9,50715 union all
select '2008-6-1 00:00',10,50715 union all
select '2008-6-1 00:00',11,50715 union all
select '2008-6-1 00:00',12,50715select
b.[date_],b.[cs_tag],b.[hour_],min(a.[hour_]) [endhour_]
from
(select * from @T t where not exists(select 1 from @T where [date_]=t.[date_] and [cs_tag]=t.[cs_tag] and [hour_]=t.[hour_]+1 ))a
,
(select * from @T t where not exists(select 1 from @T where [date_]=t.[date_] and [cs_tag]=t.[cs_tag] and [hour_]=t.[hour_]-1 ))b
where
a.[date_]=b.[date_] and a.[cs_tag]=b.[cs_tag] and b.[hour_]<=a.[hour_]
group by b.[date_],b.[cs_tag],b.[hour_]
order by b.[cs_tag],b.[date_]
(19 個資料列受到影響)
date_ cs_tag hour_ endhour_
----------------------- ----------- ----------- -----------
2008-06-14 00:00:00.000 46812 9 10
2008-06-19 00:00:00.000 46812 13 22
2008-06-20 00:00:00.000 46812 8 9
2008-06-01 00:00:00.000 50715 8 12(4 個資料列受到影響)
declare @tb table([date_] Datetime,[hour_] int,[cs_tag] int)
Insert @tb
select '2008-6-14',9,46812 union all
select '2008-6-14',10,46812 union all
select '2008-6-19',13,46812 union all
select '2008-6-19',14,46812 union all
select '2008-6-19',15,46812 union all
select '2008-6-19',16,46812 union all
select '2008-6-19',17,46812 union all
select '2008-6-19',18,46812 union all
select '2008-6-19',19,46812 union all
select '2008-6-19',20,46812 union all
select '2008-6-19',21,46812 union all
select '2008-6-19',22,46812 union all
select '2008-6-20',8,46812 union all
select '2008-6-20',9,46812 union all
select '2008-6-1',8,50715 union all
select '2008-6-1',9,50715 union all
select '2008-6-1',10,50715 union all
select '2008-6-1',11,50715 union all
select '2008-6-1',12,50715select date_,cs_tag,min(hour_),max(hour_)
from @tb
group by date_,cs_tag2008-06-14 00:00:00.000 46812 9 10
2008-06-19 00:00:00.000 46812 13 22
2008-06-20 00:00:00.000 46812 8 9
2008-06-01 00:00:00.000 50715 8 12
Insert @tb
select '2008-6-14',9,46812 union all
select '2008-6-14',10,46812 union all
select '2008-6-19',13,46812 union all
select '2008-6-19',14,46812 union all
select '2008-6-19',15,46812 union all
select '2008-6-19',16,46812 union all
select '2008-6-19',17,46812 union all
select '2008-6-19',18,46812 union all
select '2008-6-19',19,46812 union all
select '2008-6-19',20,46812 union all
select '2008-6-19',21,46812 union all
select '2008-6-19',22,46812 union all
select '2008-6-20',8,46812 union all
select '2008-6-20',9,46812 union all
select '2008-6-1',8,50715 union all
select '2008-6-1',9,50715 union all
select '2008-6-1',10,50715 union all
select '2008-6-1',11,50715 union all
select '2008-6-1',12,50715Select date_,hour_,cs_tag,0 as rn into #t
from @tb order by 1,3,2declare @i int
set @i=0
update #t set rn=hour_ - @i,@i=@i+1select date_,cs_tag,s_hour=min(hour_),e_hour=max(hour_) from #t
group by date_,cs_tag,rn2008-06-01 00:00:00.000 50715 8 12
2008-06-14 00:00:00.000 46812 9 10
2008-06-19 00:00:00.000 46812 13 22
2008-06-20 00:00:00.000 46812 8 9
from 数据表
group by date_, cs_tag