sql - 如何从中间有最大计数的表中获取 eid？

Question

我有一个具有以下结构的表员工：

Id.      Mid    Salary

1          20        200
2          20       3000
3         30       200
4         34       4000
5        30         300
6        30        400
1        23        440
1        24         333
2         21        3

我想得到如下结果：

Id     Mid
1      3

标签： sqlsql-server

Answer 1

使用前 1

select top 1 id,count(*) as cnt
from table   
group by id
order by cnt desc

如果您需要领带，请使用with ties

select top 1 with ties id,count(*) as cnt
    from table   
    group by id
    order by cnt desc