通过时间差求总时间

Question

给出下面的数据，计算出所用的总时间：
ID       工号            开始时间                         结束时间                        日期
1 70080 2010-02-22 11:57:30.000 2010-02-22 12:45:57.000 2010-02-22 00:00:00.000
2 70080 2010-02-22 17:19:24.000 2010-02-22 17:53:21.000 2010-02-22 00:00:00.000
3 70080 2010-02-22 11:53:03.000 2010-02-22 17:13:24.000 2010-02-22 00:00:00.000
4 70097 2010-02-22 11:48:43.000 2010-02-22 16:54:18.000 2010-02-22 00:00:00.000
5 70158 2010-02-22 11:12:14.000 2010-02-22 16:19:19.000 2010-02-22 00:00:00.000
6 70158 2010-02-22 11:19:46.000 2010-02-22 16:40:45.000 2010-02-22 00:00:00.000
7 70097 2010-02-22 11:13:46.000 2010-02-22 16:55:17.000 2010-02-22 00:00:00.000
8 70158 2010-02-23 11:32:06.000 2010-02-23 16:42:20.000 2010-02-23 00:00:00.000
9 70158 2010-02-23 11:33:20.000 2010-02-23 17:02:07.000 2010-02-23 00:00:00.000
10 70097 2010-02-22 11:19:53.000 2010-02-22 16:40:30.000 2010-02-22 00:00:00.000
通过怎么样的方法能将这里面的3个工号所花的总时间求出来，以HH:MM:SS的形式显示总时间。谢谢各位！！帮个忙！！！

Answer 1

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[工号] int,[开始时间] datetime,[结束时间] datetime,[日期] datetime)
insert [tb]
select 1,70080,'2010-02-22 11:57:30.000','2010-02-22 12:45:57.000','2010-02-22 00:00:00.000' union all
select 2,70080,'2010-02-22 17:19:24.000','2010-02-22 17:53:21.000','2010-02-22 00:00:00.000' union all
select 3,70080,'2010-02-22 11:53:03.000','2010-02-22 17:13:24.000','2010-02-22 00:00:00.000' union all
select 4,70097,'2010-02-22 11:48:43.000','2010-02-22 16:54:18.000','2010-02-22 00:00:00.000' union all
select 5,70158,'2010-02-22 11:12:14.000','2010-02-22 16:19:19.000','2010-02-22 00:00:00.000' union all
select 6,70158,'2010-02-22 11:19:46.000','2010-02-22 16:40:45.000','2010-02-22 00:00:00.000' union all
select 7,70097,'2010-02-22 11:13:46.000','2010-02-22 16:55:17.000','2010-02-22 00:00:00.000' union all
select 8,70158,'2010-02-23 11:32:06.000','2010-02-23 16:42:20.000','2010-02-23 00:00:00.000' union all
select 9,70158,'2010-02-23 11:33:20.000','2010-02-23 17:02:07.000','2010-02-23 00:00:00.000' union all
select 10,70097,'2010-02-22 11:19:53.000','2010-02-22 16:40:30.000','2010-02-22 00:00:00.000'
 
---查询---
select 
  工号,
  convert(varchar(8),dateadd(ss,sum(datediff(ss,开始时间,结束时间)),0),108) as 总时间 
from 
  tb 
group by 
  工号---结果---
工号          总时间
----------- --------
70080       06:42:45
70097       16:07:43
70158       21:07:05(3 行受影响)

Answer 2

create function timediff(@time int)
returns varchar(50)
AS
begin
declare @hour int, @minute int, @second int
select @second = @time%60;
select @minute = @time/60%60;
select @hour = @time/60/60%24;return + convert(varchar,@hour)+':'
     + convert(varchar,@minute)+':'
     + convert(varchar,@second)
end
GOselect dbo.timediff(SUM(DATEDIFF(ss,[开始时间],[结束时间]))
from  tb 

Answer 3

select cast(hh as varchar(10))+':'+cast(mm as varchar(10))+':'+cast(ss as varchar(10))  
from 
(select sum(datediff(s,开始时间,结束时间))/3600 hh,
(sum(datediff(s,开始时间,结束时间))-3600*(sum(datediff(s,开始时间,结束时间))/3600))/60 mm,
sum(datediff(s,开始时间,结束时间))-3600*(sum(datediff(s,开始时间,结束时间))/3600)-
60*((sum(datediff(s,开始时间,结束时间))-3600*(sum(datediff(s,开始时间,结束时间))/3600))/60) ss
from #tmp) as t 

Answer 4

改一下，小时计算有问题，向天进位了。
create function [dbo].[timediff](@time bigint)
returns varchar(50)
AS
begin
declare @hour int, @minute int, @second bigint
select @second = @time%60;
select @minute = @time/60%60;
select @hour = @time/60/60;return + convert(varchar,@hour)+':'
     + convert(varchar,@minute)+':'
     + convert(varchar,@second)
end

Answer 5

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[工号] int,[开始时间] datetime,[结束时间] datetime,[日期] datetime)
insert [tb]
select 1,70080,'2010-02-22 11:57:30.000','2010-02-22 12:45:57.000','2010-02-22 00:00:00.000' union all
select 2,70080,'2010-02-22 17:19:24.000','2010-02-22 17:53:21.000','2010-02-22 00:00:00.000' union all
select 3,70080,'2010-02-22 11:53:03.000','2010-02-22 17:13:24.000','2010-02-22 00:00:00.000' union all
select 4,70097,'2010-02-22 11:48:43.000','2010-02-22 16:54:18.000','2010-02-22 00:00:00.000' union all
select 5,70158,'2010-02-22 11:12:14.000','2010-02-22 16:19:19.000','2010-02-22 00:00:00.000' union all
select 6,70158,'2010-02-22 11:19:46.000','2010-02-22 16:40:45.000','2010-02-22 00:00:00.000' union all
select 7,70097,'2010-02-22 11:13:46.000','2010-02-22 16:55:17.000','2010-02-22 00:00:00.000' union all
select 8,70158,'2010-02-23 11:32:06.000','2010-02-23 16:42:20.000','2010-02-23 00:00:00.000' union all
select 9,70158,'2010-02-23 11:33:20.000','2010-02-23 17:02:07.000','2010-02-23 00:00:00.000' union all
select 10,70097,'2010-02-22 11:19:53.000','2010-02-22 16:40:30.000','2010-02-22 00:00:00.000'create function [dbo].[timediff](@time bigint)
returns varchar(50)
AS
begin
declare @hour int, @minute int, @second bigint
select @second = @time%60;
select @minute = @time/60%60;
select @hour = @time/60/60;return + convert(varchar,@hour)+':'
     + convert(varchar,@minute)+':'
     + convert(varchar,@second)
end---查询---select 工号,dbo.timediff(sum(datediff(ss,开始时间,结束时间))) as 总时间 
from   tb group by   工号