# SQL 记录间交叉值求解

aohan 2013-05-16 03:01:36
``````

/*

*/

--基础表准备
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中是否可以不使用游标而实现上述的求解？

``````
...全文
87 5 打赏 收藏 举报

5 条回复

``````
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
``````
• 打赏
• 举报

aohan 2013-05-16

• 打赏
• 举报

jack15850798154 2013-05-16
``````

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

``````
• 打赏
• 举报

jack15850798154 2013-05-16
``````

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

``````
• 打赏
• 举报

``````
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
``````
• 打赏
• 举报

3.3w+

MS-SQL Server相关内容讨论专区

2013-05-16 03:01