代码之家 › 专栏 › 技术社区 › MAK

先按最重复的记录排序

sql-server-2008-r2 sql-server

MAK · 技术社区 · 7 年前

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

2 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

试用 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

Dale K 7 年前

您需要按发生次数进行第一次排序 desc asc .

select name,rn 
from
(
    select name,row_number() over(partition by name order by name) rn ,
    count(*) over(partition by name) ct
    from tbl_dup
) a
order by ct desc, rn asc

推荐文章