表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 allselect 2,'00:00:13' union allselect 1,'00:00:21' union allselect 3,'00:01:01' union allselect 1,'00:00:51' union allselect 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:232 00:00:513 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:232 00:00:513 00:01:01(3 行受影响)