select count(*) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id
上面这个语句会根据不同的EmployeeId,to_i进行统计,我现在想得到如果to_id相同就取Total最大的那条记录,不要用临时表,只想一条语句搞定。类似于下面这样的语句,我知道是错的,只是表达我想要的东西:
select * from (select count(*) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id)a where a.Total in(select MAX(total) from a)
上面这个语句会根据不同的EmployeeId,to_i进行统计,我现在想得到如果to_id相同就取Total最大的那条记录,不要用临时表,只想一条语句搞定。类似于下面这样的语句,我知道是错的,只是表达我想要的东西:
select * from (select count(*) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id)a where a.Total in(select MAX(total) from a)
select max(*) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id
(select count(*) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id) a group by to_id
select count(*) as Total,EmployeeId,to_id,max(Total) as max_total
from T_ChatLog
Group By EmployeeId,to_id
where not exists(select * from T_ChatLog where to_id=t.to_id and total>t.total)
select *
from (select count(*) as Total,EmployeeId,to_id
from T_ChatLog
Group By EmployeeId,to_id) a
where not exists (select 1 from (select count(*) as Total,EmployeeId,to_id
from T_ChatLog
Group By EmployeeId,to_id) b
where to_id = a.to_id and total > a.total)
6 1 namulow
1 2 namulow
3 4 test2
5 3 abc
4 5 test2select count(*) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id
这个语句会得到上面的结果,但我希望最终返回给我的是to_id相同但Total最大的记录,如下面的结果:
Total EmployeeId to_id
6 1 namulow
5 3 abc
4 5 test2希望各位大大看仔细,不要随便回复,谢谢。
(select row_number() over(partition by to_id order by count(*) desc) as no,
count(*) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id) a
where a.no=1
*
from
(select count(1) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id)t
where
total= (select max(total) from (select count(1) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id) b where to_id = a.to_id )
*
from
(select count(1) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id)t
where
total= (select max(total) from (select count(1) as Total,EmployeeId,to_id from T_ChatLog Group By EmployeeId,to_id) b where to_id = t.to_id )
from (select distinct to_id from T_ChatLog) a
cross apply
(select top (1) EmployeeId,Count(*) from T_ChatLog where to_id=a.to_id group by EmployeeId order by Count(*) desc) b#7应该可以
哪种效率最好呢?
非常感谢3位的热心参与,三位不但技术牛,还很热心,尤其是AcHerat,能推荐别人的答案,让我佩服,好胸怀!