select rq,StartTime,EndTime, case when (StartTime>='08:49:00' and EndTime<='09:05:00') then '重合度为3' case when (StartTime>='...' and EndTime<='...') then ... case when (StartTime>='...' and EndTime<='...') then ... case when (StartTime>='...' and EndTime<='...') then ... case when (StartTime>='' and EndTime<='') then ... from 表 这样?
declare @t table(rq varchar(10),StartTime varchar(10),EndTime varchar(10)) insert into @t select '2009-7-10','08:40:00','09:05:00' insert into @t select '2009-7-10','08:45:00','09:17:00' insert into @t select '2009-7-10','08:49:00','09:23:00'select m.rq,m.StartTime,m.EndTime,isnull(count(n.rq),0) as 重合度 from (select a.rq,a.StartTime,min(b.EndTime) as EndTime from (select rq,StartTime from @t union select rq,EndTime from @t union select distinct rq,'00:00:00' from @t) a, (select rq,EndTime from @t union select rq,StartTime from @t union select distinct rq,'24:00:00' from @t) b where a.rq=b.rq and a.StartTime<b.EndTime group by a.rq,a.StartTime) m left join @t n on m.rq=n.rq and m.StartTime between n.StartTime and n.EndTime and m.EndTime between n.StartTime and n.EndTime group by m.rq,m.StartTime,m.EndTime order by 重合度 desc/* rq StartTime EndTime 重合度 ---------- ---------- ---------- ----------- 2009-7-10 08:49:00 09:05:00 3 2009-7-10 08:45:00 08:49:00 2 2009-7-10 09:05:00 09:17:00 2 2009-7-10 09:17:00 09:23:00 1 2009-7-10 08:40:00 08:45:00 1 2009-7-10 00:00:00 08:40:00 0 2009-7-10 09:23:00 24:00:00 0 */
select * , (select count(1) from datatable b where b.rq+b.starttime < a.rq+a.endtime and b.rq+b.endtime > a.rq+a.starttime) - 1 as [重合度] from datatable a
set nocount on -- 基础数据 declare @t table(rq varchar(10),StartTime varchar(10),EndTime varchar(10)) insert into @t select '2009-7-10','08:40:00','09:05:00' insert into @t select '2009-7-10','08:45:00','09:17:00' insert into @t select '2009-7-10','08:49:00','09:23:00'-- 转换为标准的DateTime declare @tb table(bgnTime datetime, endTime datetime) insert into @tb select rq+' '+starttime, rq+' '+endtime from @t-- 所有的时间点 declare @tm table (timepoint datetime) insert into @tm select bgntime from @tb union select endtime from @tb-- 时间分段 declare @tmblock table (bgntime datetime, endtime datetime) insert into @tmblock select a.timepoint , b.timepoint from @tm a join @tm b on a.timepoint < b.timepoint where 1=1 and not exists ( select 1 from @tm c where c.timepoint>a.timepoint and c.timepoint<b.timepoint)-- 各个时段的重合度 select * , (select count(1) from @tb b where b.bgntime<a.endtime and b.endtime>a.bgntime) as [重合度] from @tmblock a-- bgntime|endtime|重合度 -- 2009-07-10 08:40:00.000|2009-07-10 08:45:00.000|1 -- 2009-07-10 08:45:00.000|2009-07-10 08:49:00.000|2 -- 2009-07-10 08:49:00.000|2009-07-10 09:05:00.000|3 -- 2009-07-10 09:05:00.000|2009-07-10 09:17:00.000|2 -- 2009-07-10 09:17:00.000|2009-07-10 09:23:00.000|1 --
表结构:
日期:Rq(Datetime,8),
起始时间:StartTime(Datetime,8),
截止时间:EndTime(Datetime,8)
数据如举例所示:
rq StartTime EndTime
2009-7-10 08:40:00 09:05:00
2009-7-10 08:45:00 09:17:00
2009-7-10 08:49:00 09:23:00
希望得到如下结果:
时间段 重合度
08:49:00-09:05:00 3
08:45:00-08:49:00 2
09:05:00-09:17:00 2
08:40:00-08:45:00 1
09:17:00-09:23:00 1
00:00:00-08:40:00 0
09:23:00-24:00:00 0
case when (StartTime>='08:49:00' and EndTime<='09:05:00')
then '重合度为3'
case when (StartTime>='...' and EndTime<='...')
then ...
case when (StartTime>='...' and EndTime<='...')
then ...
case when (StartTime>='...' and EndTime<='...')
then ...
case when (StartTime>='' and EndTime<='')
then
...
from 表 这样?
insert into @t select '2009-7-10','08:40:00','09:05:00'
insert into @t select '2009-7-10','08:45:00','09:17:00'
insert into @t select '2009-7-10','08:49:00','09:23:00'select
m.rq,m.StartTime,m.EndTime,isnull(count(n.rq),0) as 重合度
from
(select
a.rq,a.StartTime,min(b.EndTime) as EndTime
from
(select rq,StartTime from @t union select rq,EndTime from @t union select distinct rq,'00:00:00' from @t) a,
(select rq,EndTime from @t union select rq,StartTime from @t union select distinct rq,'24:00:00' from @t) b
where
a.rq=b.rq and a.StartTime<b.EndTime
group by
a.rq,a.StartTime) m
left join
@t n
on
m.rq=n.rq and m.StartTime between n.StartTime and n.EndTime and m.EndTime between n.StartTime and n.EndTime
group by
m.rq,m.StartTime,m.EndTime
order by
重合度 desc/*
rq StartTime EndTime 重合度
---------- ---------- ---------- -----------
2009-7-10 08:49:00 09:05:00 3
2009-7-10 08:45:00 08:49:00 2
2009-7-10 09:05:00 09:17:00 2
2009-7-10 09:17:00 09:23:00 1
2009-7-10 08:40:00 08:45:00 1
2009-7-10 00:00:00 08:40:00 0
2009-7-10 09:23:00 24:00:00 0
*/
(select count(1) from datatable b
where b.rq+b.starttime < a.rq+a.endtime
and b.rq+b.endtime > a.rq+a.starttime) - 1 as [重合度]
from datatable a
-- 基础数据
declare @t table(rq varchar(10),StartTime varchar(10),EndTime varchar(10))
insert into @t select '2009-7-10','08:40:00','09:05:00'
insert into @t select '2009-7-10','08:45:00','09:17:00'
insert into @t select '2009-7-10','08:49:00','09:23:00'-- 转换为标准的DateTime
declare @tb table(bgnTime datetime, endTime datetime)
insert into @tb
select rq+' '+starttime, rq+' '+endtime
from @t-- 所有的时间点
declare @tm table (timepoint datetime)
insert into @tm
select bgntime from @tb
union
select endtime from @tb-- 时间分段
declare @tmblock table (bgntime datetime, endtime datetime)
insert into @tmblock
select a.timepoint , b.timepoint
from @tm a
join @tm b on a.timepoint < b.timepoint
where 1=1
and not exists (
select 1 from @tm c
where c.timepoint>a.timepoint
and c.timepoint<b.timepoint)-- 各个时段的重合度
select * ,
(select count(1) from @tb b
where b.bgntime<a.endtime and b.endtime>a.bgntime) as [重合度]
from @tmblock a-- bgntime|endtime|重合度
-- 2009-07-10 08:40:00.000|2009-07-10 08:45:00.000|1
-- 2009-07-10 08:45:00.000|2009-07-10 08:49:00.000|2
-- 2009-07-10 08:49:00.000|2009-07-10 09:05:00.000|3
-- 2009-07-10 09:05:00.000|2009-07-10 09:17:00.000|2
-- 2009-07-10 09:17:00.000|2009-07-10 09:23:00.000|1
--