mysql排序|并列排序|占位排序|分组排序

现有表结构

CREATE TABLE `user` (
  `id` int NOT NULL,
  `username` varchar(50) NOT NULL DEFAULT '',
  `gender` char(50) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

数据

根据score进行排序

取前十名

取前十名

# 不占位排序
SELECT
    u.id,
    u.username,
    u.gender,
    u.score,
    @rank := @rank + 1 
FROM
    ( SELECT * FROM user ORDER BY score DESC ) u,
    ( SELECT @rank := 0 ) r HAVING @rank < 10;

--

并列排序

并列排序

# 并列排序
SELECT
    u.id,
    u.username,
    u.gender,
    u.score,
    CASE 
        WHEN @score=u.score THEN
            @rank
        WHEN @score:=u.score THEN
            @rank:= @rank + 1
        ELSE
            1
    END as topnumber
FROM
    ( SELECT * FROM user ORDER BY score DESC ) u,
    ( SELECT @rank := 0, @score:=NULL ) r HAVING topnumber < 10;

--

占位并列排序

占位并列排序

# 占位并列排序
SELECT
    u.id,
    u.username,
    u.gender,
    u.score,
    @rank := @rank + 1,
    @last :=CASE
        WHEN @score = u.score THEN
        @last 
        WHEN @score := u.score THEN
        @rank ELSE 1000 
    END AS topnumber 
FROM
    ( SELECT * FROM user ORDER BY score DESC ) u,
    ( SELECT @rank := 0, @score := NULL, @last := NULL ) r;

分组后排序

分组后排序

# 占位并列排序
SELECT
    u.id,
    u.username,
    u.gender,
    u.score,#@rank := @rank + 1,
    #rn,
    @last :=CASE
        WHEN @score = u.score THEN
        @last 
        WHEN @score := u.score THEN
        rn ELSE -1 
    END AS topnumber 
FROM
    (SELECT id, username, gender, score, row_number() over ( PARTITION BY gender ORDER BY score DESC ) rn FROM user 
    ) u,
    ( SELECT @rank := 0, @score := 0, @last := 0 ) r 
HAVING
    topnumber < 10;