sql-server - 先按最多重复记录排序

create table tbl_dup
(
    name varchar(100)
);

insert into tbl_dup values('Arsel Rous'),('Oram Rock'),('Oram Rock'),('Brown Twor'),
                          ('John Mak'),('Mak Dee'),('Smith Will'),('Mak Dee'),
                          ('John Mak'),('Oram Rock'),('John Mak'),('Oram Rock');

查询:我正在寻找应在结果集中显示在第一级的重复记录的排序。

select *
from
(
select name,row_number() over(partition by name order by name) rn
from tbl_dup
) a
order by name,rn;

获得:

name        rn
--------------
Arsel Rous  1
Brown Twor  1
John Mak    1
John Mak    2
John Mak    3
Mak Dee     1
Mak Dee     2
Oram Rock   1
Oram Rock   2
Oram Rock   3
Oram Rock   4
Smith Will  1

预期结果:

name        rn
---------------
Oram Rock   1
Oram Rock   2
Oram Rock   3
Oram Rock   4
John Mak    1
John Mak    2
John Mak    3
Mak Dee     1
Mak Dee     2
Arsel Rous  1
Brown Twor  1

最佳答案

尝试在 COUNT 子句中使用 ORDER BY 作为解析函数:

SELECT name, ROW_NUMBER() OVER (PARTITION by name ORDER BY name) rn
FROM tbl_dup
ORDER BY COUNT(*) OVER (PARTITION BY name) DESC, rn;

Demo

关于sql-server - 先按最多重复记录排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54977348/