PARTITION BY 用于对数据进行分区（分组），使窗口函数在每个分区内独立计算，而不会影响其他分区的数据。分区函数一般与排名函数一起使用。

score表

数据

id	userId	score
1	1	100
2	2	90
3	3	95
4	1	92
5	2	92
6	3	100

sql语句

DROP TABLE if EXISTS score;


CREATE TABLE score (
	id int PRIMARY KEY AUTO_INCREMENT,
	userId int NOT NULL,
	score int NOT NULL
);

INSERT INTO score (userId, score)
VALUES (1, 100),
	(2, 90),
	(3, 95),
	(1, 92),
	(2, 92),
	(3, 100);

不分组 RANK

SELECT *, RANK() OVER (ORDER BY score DESC) AS `rank` FROM score;

id	userId	score	rank
1	1	100	1
6	3	100	1
3	3	95	3
4	1	92	4
5	2	92	4
2	2	90	6

分组后 RANK

SELECT *, RANK() OVER (PARTITION BY userId ORDER BY score DESC) AS `rank` FROM score;

id	userId	score	rank
1	1	100	1
4	1	92	2
5	2	92	1
2	2	90	2
6	3	100	1
3	3	95	2

获取每个分组的前1(n)条数据

SELECT *
FROM (
	SELECT *, RANK() OVER (PARTITION BY userId ORDER BY score DESC) AS `rank`
	FROM score
) t
WHERE t.rank <= 1;

版权所有

许可证：署名-相同方式共享 4.0 国际 (CC-BY-SA-4.0)