SQL 记录间交叉值求解

aohan 2013-05-16 03:01:36


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


...全文
127 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
习惯性蹭分 2013-05-16
  • 打赏
  • 举报
回复

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
  • 打赏
  • 举报
回复
已受到启发,Cross join后,确实会形成每一条记录之间的对应关系
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

哥眼神纯洁不 2013-05-16
  • 打赏
  • 举报
回复

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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧