请教：如何获取每个时间累加全部时间。
想获得全部时间行累加的时间，结果如：72:05:22
数据库是：MSSQL 2005
[最优解释]
create table tb(dt varchar(10))
insert into tb select '20:05:27'
insert into tb select '12:40:34'
insert into tb select '03:55:29'
insert into tb select '11:12:16'
insert into tb select '17:25:44'
insert into tb select '23:48:39'
go
select ltrim(H+(m+s/60)/60)+':'+right('00'+ltrim((m+s/60)%60),2)+':'+right('00'+LTRIM(s%60),2) from (
select sum(datepart(hh,dt))h,sum(datepart(mi,dt))m,sum(DATEPART(s,dt))s from tb
)t
/*
----------------------
89:08:09
(1 行受影响)
*/
go
drop table tb
[其他解释]
楼上不对，如果超过24小时又会变为0
正确的如下：
with timetable AS
(
select Convert(VARCHAR(30), GETDATE(), 108) AS dt union all
select Convert(VARCHAR(30), GETDATE(), 108) union all
select Convert(VARCHAR(30), GETDATE(), 108) union all
select Convert(VARCHAR(30), GETDATE(), 108) union all
select Convert(VARCHAR(30), GETDATE(), 108) union all
select Convert(VARCHAR(30), GETDATE(), 108)
)
SELECT CONVERT(VARCHAR(10),sum(DATEDIFF(SECOND,'00:00:00',dt))/3600)+':'+CONVERT(VARCHAR(10),sum(DATEDIFF(SECOND,'00:00:00',dt))%3600/60)+':'+CONVERT(VARCHAR(10),sum(DATEDIFF(SECOND,'00:00:00',dt))%3600%60)+''
FROM timetable
select sum(datepart(hh,dt))h,sum(datepart(mi,dt))m,sum(DATEPART(s,dt))s from tb
)t
/*
----------------------
18:27:49
(1 行受影响)
*/
go
drop table tb
[其他解释]
declare @t table(Ftime char(8))
insert into @t select '08:12:33'
insert into @t select '10:50:16'
insert into @t select '00:00:30'
insert into @t select '01:00:22'
insert into @t select '00:09:15'
insert into @t select '00:10:18'
insert into @t select '01:00:50'
insert into @t select '00:00:40'
insert into @t select '00:00:30'
--测试
select convert(char(8),dateadd(ss,sum(datediff(ss,0,cast(Ftime as datetime))),0),108) As TimeSum
from @t
[其他解释]
create table #tb(dt datetime)
insert into #tb select '00:05:07'
insert into #tb select '02:20:34'
insert into #tb select '03:55:29'
insert into #tb select '01:12:16'
insert into #tb select '07:15:44'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '03:38:39'
insert into #tb select '10:00:00'
go
declare @t datetime
select @t = cast(sum(CAST(dt AS float)) as datetime) from #tb
select cast(datediff(hh,'1900-01-01',@T)as varchar(10))+':'+cast(datepart(mi,@T)as varchar(10))+':'+cast(datepart(ss,@T)as varchar(10))
/*--------------------------------
43:2:25
(1 行受影响)
*/
