找出相同用户名通话时长最长的记录。如有下表: 用户 通话时长
A1 00:03:01 B1 00:00:51 A1 00:04:01 A2 00:03:01 B1 00:01:20 B2 00:05:51
D1 00:30:03
拜托各位大侠了
A1 00:03:01 B1 00:00:51 A1 00:04:01 A2 00:03:01 B1 00:01:20 B2 00:05:51
D1 00:30:03
拜托各位大侠了
from 表名
group by 用户
select 用户,max(通话时长) from tb group by 用户
declare @t table (用户 varchar(2),通话时长 datetime)
insert into @t
select 'A1','00:03:01' union all
select 'B1','00:00:51' union all
select 'A1','00:04:01' union all
select 'A2','00:03:01' union all
select 'B1','00:01:20' union all
select 'B2','00:05:51' union all
select 'D1','00:30:03'select 用户,通话时长=
convert(varchar(8),max(通话时长),108) from @t group by 用户
/*
用户 通话时长
---- --------
A1 00:04:01
A2 00:03:01
B1 00:01:20
B2 00:05:51
D1 00:30:03
*/
用户,通话时长
from
(select 用户,通话时长,row=row_number()order by (partition by 用户 order by 通话时长 desc) from table1 )t
where row=1