CREATE TABLE times(
[a] [datetime],
[b] [datetime]
)
--下面的数据去重复求和是3小时。我怎么能查出3来?
insert into times(a,b) values('2010-01-01 9:00:00','2010-01-01 10:00:00')
insert into times(a,b) values('2010-01-01 8:00:00','2010-01-01 9:30:00')
insert into times(a,b) values('2010-01-01 11:00:00','2010-01-01 12:00:00')
------解决方案--------------------
--sql2012
CREATE TABLE times(
[a] [datetime],
[b] [datetime]
)
--下面的数据去重复求和是3小时。我怎么能查出3来?
insert into times(a,b) values('2010-01-01 9:00:00','2010-01-01 10:00:00')
insert into times(a,b) values('2010-01-01 8:00:00','2010-01-01 9:30:00')
insert into times(a,b) values('2010-01-01 11:00:00','2010-01-01 12:00:00')
--method one
with a as(
select min(a) as a,max(b) as b from times
)
select a,b,datediff(hour,a,b) as '和' from a
--结果展示(去重复后求和是4小时)
/*
a b 和
----------------------- ----------------------- -----------
2010-01-01 08:00:00.000 2010-01-01 12:00:00.000 4
(1 行受影响)
*/
--method two
select sum(datediff(hour,a,b))as '和'
from times
--结果展示
/*
和
-----------
3
(1 行受影响)
*/
/*
楼主没有说明,什么去重复求和,所以无法给出肯定的答案,仅供参考
*/