日期:2014-05-18  浏览次数:20504 次

SQL时间相加语句
表user
ID time
1 00:00:11
2 00:00:13  
1 00:00:21
3 00:01:01
1 00:00:51
2 00:00:38
sql语句得到结果
1 00:01:23
2 00:00:51
3 00:01:01
这个SQL语句怎么写啊?

------解决方案--------------------
SQL code

--> 测试数据:[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[ID] int,
[time] time
)
insert [test]
select 1,'00:00:11' union all
select 2,'00:00:13' union all
select 1,'00:00:21' union all
select 3,'00:01:01' union all
select 1,'00:00:51' union all
select 2,'00:00:38'

select 
    id,
    convert(varchar(8),dateadd(second,SUM(DATEDIFF(second,'00:00:00',[time])),'00:00:00'),24) as [time]
from 
    test 
group by 
    id
/*
id    time
-----------------------
1    00:01:23
2    00:00:51
3    00:01:01
*/

------解决方案--------------------
SQL code

declare @user table 
(ID        int            not null,
 Time    varchar(10)    not null)
 
 insert into @user
 select 1,'00:00:11' union all
 select 2,'00:00:13' union all
 select 1,'00:00:21' union all
 select 3,'00:01:01' union all
 select 1,'00:00:51' union all
 select 2,'00:00:38' 
 ;with t as 
 (
    select *,datediff(s,'1900-01-01 00:00:00.00',convert(datetime,time)) as S from @user
 )
 select id,right(convert(varchar(19),dateadd(s,sum(s),'1900-01-01 00:00:00.00'),120),8) as Time from T
 group by ID
 
------------------

(6 行受影响)
id          Time
----------- ----------------
1           00:01:23
2           00:00:51
3           00:01:01

(3 行受影响)