MySQL分组取前N名

发表于 | 分类于 |

1 使用变量

8.0版本中标记SET外变量赋值为弃用，预计9.0版本移除。

可以使用内建函数替换。

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

注意：变量间冲突问题。

(1) 非并列排名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
	*, 
	@cur:= @cur + 1 AS 'rank'
FROM 
	players, 
	(SELECT @cur := 0) AS r
ORDER BY age;

# 分组，通过在排序中增加组别，并记录组内排名实现
SELECT 
	*
FROM
	(SELECT 
		*,
		IF(@dep = depart, @cur := @cur + 1, @cur := 1) AS 'rank',
		@dep := depart
	FROM 
		players,
		(SELECT @dep := NULL, @cur := 0) AS r
	ORDER BY depart, age
	) AS s;

:=用于赋值，而=仅在SET或UPDATE时才是复制，其余为相等

(SELECT @cur := 0) AS r 创建变量cur用于记录当前记录排名。将变量作为表查出，以便递增。提取的表需要别名。

(2) 并列排名

1) 不连续排名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT
	*
FROM 
	(SELECT
     	*,
     	@cur := IF(
            @pre = age,
            @cur,
            @inc
        ) AS 'rank',
     	@inc := @inc + 1,
     	@pre := age
     FROM
     	players,
     	(SELECT @cur := 0, @pre := NULL, @inc := 1) AS r
	 ORDER BY age
    ) AS s;
    
# 分组
SELECT
	*
FROM
	(SELECT 
     	*,
     # 不赋值，可能导致前两个同部门同值时排名仍为0
		@cur := IF(@dep = depart,
    		IF(@pre = age, @cur, @inc),
   			@inc := 1
        ) AS 'rank',
		@dep := depart,
		@pre := age,
     	@inc := @inc + 1
	FROM 
    	players,
     	(SELECT @dep := NULL, @pre := NULL, @cur := 0, @inc := 1) AS r
	ORDER BY depart, age 
	) AS s;

cur记录当前排名位置，inc记录下一排名位置，pre记录上一变量值

IF() 测试第一个表达式，true时返回第二个值，false时返回第三个值

思考：可以看做SELECT字段从上到下依次取值。

2) 连续排名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT 
	*,
	IF(@pre = age, @cur, @cur := @cur + 1) AS 'rank',
	@pre = age
FROM 
	players, 
	(SELECT @cur := 0, @pre := NULL) AS r
ORDER BY age;

# 分组
SELECT
	*
FROM
	(SELECT 
     	*,
		IF(@dep = depart,
    		IF(@pre = age, @cur, @cur := @cur + 1),
   			@cur := 1
        ) AS 'rank',
		@dep := depart,
		@pre := age
	FROM 
    	players,
     	(SELECT @dep := NULL, @pre := NULL, @cur := 0) AS r
	ORDER BY depart, age 
	) AS s;

cur用于保存排名，pre用于保存变量值

rank在MySQL 8.0中是函数名，需要加引号

2 使用函数

RANK()函数实现非连续排名，使用DENSE_RANK()实现连续排名

(1) 非并列排名

1
2
3
4
5
6
7
8
9
10
SELECT
	*,
	ROW_NUMBER() OVER (ORDER BY age) AS 'rank'
FROM players;

# 分组
SELECT
	*,
	ROW_NUMBER() OVER (PARTITION BY depart ORDER BY age) AS 'rank'
FROM players;

(2) 并列排名

1) 不连续排名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
	*,
	RANK() OVER w AS 'rank'
FROM players
WINDOW w AS (ORDER BY age);

# 或

SELECT
	*,
	RANK() OVER (ORDER BY age) AS 'rank'
FROM players;

# 分组
SELECT
	*,
	RANK() OVER (PARTITION BY depart ORDER BY age) AS 'rank'
FROM players;

2) 连续排名

1
2
3
4
5
6
7
8
9
10
SELECT
	*,
	DENSE_RANK() OVER (ORDER BY age) AS 'rank'
FROM players;

# 分组
SELECT
	*,
	DENSE_RANK() OVER (PARTITION BY depart ORDER BY age) AS 'rank'
FROM players;

WINDOW操作和聚合操作的区别是WINDOW操作的结果还是单条记录，而聚合操作的是合并为一条记录

3 排名并取前N名

(1) 筛选

1
2
3
4
5
6
7
8
9
10
# 取前两名
SELECT
*
FROM
	(SELECT
		*,
		RANK() OVER (PARTITION BY depart ORDER BY age) AS 'rank'
	FROM players
    ) AS p
WHERE p.rank <= 2;

(2) 分组比较大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 没有名次显示
SELECT
	*
FROM
	players AS p
WHERE
	(SELECT
    	COUNT(*)
    FROM
    	players
    WHERE
    	depart = p.depart AND age < p.age
    ) < 2
ORDER BY depart, age;

参考资料

介绍mysql 的三种排名，由简到难

MySQL排名、分组后组内排名、取各组的前几名

mysql分组取每组前几条记录(排序)

在MySQL中实现Rank高级排名函数

RANK()

:=和=的区别

mysql中if（）函数使用

数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE players (
  pid int(2) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  age int(2) NOT NULL,
  PRIMARY KEY (pid),
  UNIQUE KEY name (name)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO players (pid, name, age) VALUES
 (1, 'Samual', 25),
 (2, 'Vino', 20),
 (3, 'John', 20),
 (4, 'Andy', 22),
 (5, 'Brian', 21),
 (6, 'Dew', 24),
 (7, 'Kris', 25),
 (8, 'William', 26),
 (9, 'George', 23),
 (10, 'Peter', 19),
 (11, 'Tom', 20),
 (12, 'Andre', 20);
 
ALTER TABLE players ADD depart varchar(10) NOT NULL;

UPDATE players SET depart = 
	CASE FLOOR(3 * RAND())
		WHEN 0 THEN 'dev'
		WHEN 1 THEN 'fia'
		WHEN 2 THEN 'man'
	END;