Question

SQL 分组统计并排序

要求如下：
表：table1
字段：val
值：（如下)
2
2
2
4
4
5
5
统计val 并按val分组，然后排列前2名，最后显示得到的val值，用row_number() over.
这是我做的，但是会返回3条数据（2,4,5），因为4,5并列第二
select top 3 num from (select val, count(1) as num from table1 group by val) order by num desc

Answer 1

with tmp as
(select row_number() over(order by count(val)) as rn,val,count(val) as cnum from table1 group by val)
select * from tmp where rn<3
要想区别并列现象还要复杂一些，可能简单的语句解决不了

Answer 2

select top 3 val,count(1) from table1 order by num desc group by val,num order by num desc

Answer 3

select top 2 val from (select val, count(1) as num from #table1 group by val) as table2 order by num desc,val