日期:2014-05-17  浏览次数:20480 次

SQL 记录间交叉值求解


/*
有如下的时间点表,其中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中是否可以不使用游标而实现上述的求解?


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

------解决方案--------------------


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