通过时间差求总时间

[jiangzhong610]

给出下面的数据，计算出所用的总时间：
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的形式显示总时间。谢谢各位！！帮个忙！！！

[百年树人]

---测试数据---
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 行受影响)

[hokor]

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
GO

select dbo.timediff(SUM(DATEDIFF(ss,[开始时间],[结束时间]))
from  tb 

[duanzhi1984]

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

 

[hokor]

[Quote=引用 1 楼 josy 的回复:]
SQL code
---测试数据---
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 ……
[/Quote]
如果超过24小时就有问题了吧？

[Mr_Nice]

[Quote=引用 1 楼 josy 的回复:]

SQL code
---测试数据---
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:5……
[/Quote]

up

[hokor]

[Quote=引用 2 楼 hokor 的回复:]
SQL code
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 = @t……
[/Quote]
改一下，小时计算有问题，向天进位了。

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

[hokor]

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   工号

[百年树人]

[Quote=引用 4 楼 hokor 的回复:]
引用 1 楼 josy 的回复:
SQL code
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[工号] int,[开始时间] datetime,[结束时间] datetime,[日期] datetime)
insert [tb]
select 1,……
[/Quote]

超过24小时的，要看楼主要什么格式的，调整一下即可。