Sql计算总时间,很急!在线等,求各位高手指点
数据如下:
ID 编号 开始时间 结束时间
1 20001 9:30 11:30
2 20001 10:00 11:00
3 20001 10:30 12:00
4 20001 12:30 13:00
5 20001 12:30 13:30
6 20001 14:00 16:00
7 20002 10:00 12:00
8 20003 11:00 12:00
9 20003 11:30 15:00
如何得到如下数据:
编号 总时间
20001 5.5
20002 2
20003 4
------解决方案--------------------create table tab (ID int,编号 int,开始时间 datetime,结束时间 datetime)
insert tab
select 1 , 20001 , '9:30 ' , '11:30 '
union select 2 , 20001 , '10:00 ' , '11:00 '
union select 3 , 20001 , '10:30 ' , '12:00 '
union select 4 , 20001 , '12:30 ' , '13:00 '
union select 5 , 20001 , '12:30 ' , '13:30 '
union select 6 , 20001 , '14:00 ' , '16:00 '
union select 7 , 20002 , '10:00 ' , '12:00 '
union select 8 , 20003 , '11:00 ' , '12:00 '
union select 9 , 20003 , '11:30 ' , '15:00 '
select ID=IDENTITY(INT,1,1),编号,开始时间 INTO #L1
from tab t
where not exists(select 1 from tab where 编号=t.编号 and t.开始时间 between 开始时间 and 结束时间 and id <t.id)
select * into #lsb
from ( select id,编号,开始时间,结束时间
from tab t
where not exists(select 1 from tab where 编号=t.编号 and 结束时间> =t.结束时间 and id <t.id)) d
select ID=IDENTITY(INT,1,1),编号,结束时间 INTO #L2
from #lsb d
where NOT exists(select 1 from #lsb where 编号=d.编号 and id> d.id and 开始时间 between d.开始时间 and d.结束时间)
SELECT A.编号,总时间=SUM(DATEDIFF(minute,A.开始时间,B.结束时间)*1.0/60)
FROM #L1 A INNER JOIN #L2 B ON A.ID=B.ID
GROUP BY A.编号
DROP TABLE #lsb,#L1,#L2
DROP TABLE TAB
/* 看看这个结果
编号
----------- ----------------------------------------
20001 5.500000
20002 2.000000
20003 4.000000
(3 row(s) affected)
*/