SQL 记录间交叉值求解

[aohan]

/*
有如下的时间点表，其中T1与T2中的记录数数量是不定的，比如T2中的记录可能有多条存在，也可能不存在，希望计算多记录之间的交集，如何用SQL实现 
*/

--基础表准备
create table T1(startdate datetime ,enddate datetime )
create table T2(startdate datetime ,enddate datetime )

--测试
insert into t1('2013-05-01 8:00:00','2013-05-01 12:00:00')    --序号1
insert into t1('2013-05-01 13:00:00','2013-05-01 17:00:00')   --序号2

insert into t2('2013-05-01 7:30:00','2013-05-01 09:00:00')    --序号3
insert into t2('2013-05-01 09:30:00','2013-05-01 12:00:00')   --序号4
insert into t2('2013-05-01 14:00:00','2013-05-01 17:00:00')   --序号5

--求解
-- 序号1与序号3 求交叉值是 8:00:00~9:00:00 ，希望得出1小时
-- 序号1与序号4 求交叉值是 9:30:00~12:00:00，希望得出3小时
-- 序号1与序号5 求交叉值不存在，希望得出0小时

-- 序号2与序号3 求交叉值不存在，希望得出0小时
-- 序号2与序号4 求交叉值不存在，希望得出0小时
-- 序号2与序号5 求交叉值 14:00:00~17:00:00，希望得出3小时

--最终希望得出的数值是所有数值之和，即1+3+3=7个小时

--上面的求解过程，是一个循环求解的方式，在SQl中是否可以不使用游标而实现上述的求解？

[哥眼神纯洁不]

select sum(case when (datediff(hh,
case when t1.startdate>t2.startdate then t1.startdate else t2.startdate end,
case when t1.enddate<t2.enddate then t1.enddate else t2.enddate end))<0 then 0 else 
datediff(hh,
case when t1.startdate>t2.startdate then t1.startdate else t2.startdate end,
case when t1.enddate<t2.enddate then t1.enddate else t2.enddate end) end)
from t1,t2

• 打赏
• 举报

回复 赞

[jack15850798154]

SELECT CASE WHEN  a.startdate>=b.startdate AND a.enddate>=b.enddate AND a.startdate<=b.enddate THEN DATEDIFF(hh,a.startdate,b.enddate)
            WHEN  a.startdate<=b.startdate AND a.enddate>=b.enddate THEN DATEDIFF(hh,b.startdate,b.enddate)
            ELSE 0 END
  FROM T1 a 
CROSS JOIN T2 b

• 打赏
• 举报

回复 赞

[jack15850798154]

SELECT SUM( CASE WHEN  a.startdate>=b.startdate AND a.enddate>=b.enddate AND a.startdate<=b.enddate THEN DATEDIFF(hh,a.startdate,b.enddate)
            WHEN  a.startdate<=b.startdate AND a.enddate>=b.enddate THEN DATEDIFF(hh,b.startdate,b.enddate)
            ELSE 0 END
) AS AmountSum
  FROM T1 a 
CROSS JOIN T2 b

• 打赏
• 举报

回复 赞

[aohan]

已受到启发,Cross join后，确实会形成每一条记录之间的对应关系

• 打赏
• 举报

回复 赞

[习惯性蹭分]

if OBJECT_ID('t1') is not null
drop table t1
if object_id('t2') is not null
drop table t2
go
create table T1(startdate datetime ,enddate datetime )
create table T2(startdate datetime ,enddate datetime )

insert into t1 values('2013-05-01 8:00:00','2013-05-01 12:00:00')    --序号1
insert into t1 values('2013-05-01 13:00:00','2013-05-01 17:00:00')   --序号2
 
insert into t2 values('2013-05-01 7:30:00','2013-05-01 09:00:00')    --序号3
insert into t2 values('2013-05-01 09:30:00','2013-05-01 12:00:00')   --序号4
insert into t2 values('2013-05-01 14:00:00','2013-05-01 17:00:00') 
;with sel as(
select startdate=case when t1.startdate>t2.startdate then t1.startdate else t2.startdate end,
enddate=case when t1.enddate<t2.enddate then t1.enddate else t2.enddate end from t1 cross join t2)
select [hour]=case when DATEDIFF(hour,startdate,enddate)>0 then DATEDIFF(HOUR,startdate,enddate) else 0 end from sel 

• 打赏
• 举报

回复 赞