mysql实现分组后每组取前N条

CREATE TABLE t_stu_score(
stu_id CHAR(4),   -- 学生ID
class_id INT,        -- 课程ID
score INT,        -- 分数
PRIMARY KEY (stu_id,class_id)
);

-- 表中数据如下
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('001',1,97);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('001',2,50);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('001',3,70);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('002',1,92);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('002',2,80);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('002',3,30);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('003',1,93);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('003',2,95);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('003',3,85);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('004',1,73);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('004',2,78);
INSERT INTO t_stu_score(stu_id,class_id,score) VALUES('004',3,87);

不分组

不分组&&普通排名：从1开始，顺序往下排

SELECT
	t_stu_score.*,
	@rank := @rank + 1 AS rank 
FROM
	t_stu_score,
	( SELECT @rank := 0 ) r 
ORDER BY
	score DESC

不分组&&并列排名：相同的值是相同的排名(1,2,2,3)

SELECT
	t_stu_score.*,
CASE		
		WHEN @scoreTemp = score THEN @rank 
		WHEN @scoreTemp := score THEN @rank := @rank + 1 
	END rank 
FROM
	t_stu_score,
	( SELECT @rank := 0,@scoreTemp := NULL ) r 
ORDER BY
	score DESC

不分组&&并列排名：相同的值名次相同，与上例中的并列排名不同(1,2,2,4)

SELECT
	temp.stu_id, temp.class_id, temp.rank
FROM
	(
	SELECT
		t_stu_score.*,
		@rankInit := IF( @scoreTemp = score, @rankInit, @rank ) AS rank,
		@scoreTemp := score,
		@rank := @rank + 1 
	FROM
		t_stu_score,
		( SELECT @scoreTemp := NULL, @rank := 1, @rankInit := 0 ) r 
	ORDER BY
		score DESC 
	) temp

分组

分组&&普通排名：：从1开始，顺序往下排

SELECT temp.class_id,  temp.stu_id, temp.score, temp.rank 
FROM
	(
	SELECT
	 t_stu_score.*,
	 IF ( @classIdTemp = class_id, @rank := @rank + 1, @rank := 1  ) AS rank,
   @classIdTemp := class_id 
	FROM
		t_stu_score,
		( SELECT @classIdTemp := NULL, @rank := 0  ) r 
	ORDER BY class_id ASC,score DESC
	) temp;

分组&&并列排名：相同的值是相同的排名 (1,2,2,3)

SELECT temp.class_id, temp.stu_id, temp.score, temp.rank 
FROM
	(
	SELECT
	 	t_stu_score.*,
		IF(@classIdTemp = class_id, CASE WHEN @scoreTemp = score THEN @Rank  WHEN @scoreTemp := score THEN @rank := @rank + 1 END, @rank := 1 ) AS rank,
		@classIdTemp := class_id,
		@scoreTemp := score
	FROM
		t_stu_score,
		( SELECT @classIdTemp := NULL, @scoreTemp := NULL ) r 
	ORDER BY class_id ASC,score DESC
	) temp;

分组&&并列排名：组内相同数值排名相同(1,2,2,4)

SELECT temp.class_id, temp.stu_id, temp.score, temp.rank 
FROM
	(
	SELECT
	 	t_stu_score.*,
		@rankInit := IF( @scoreTemp = score, @rankInit, @rank ) AS rank,
		@scoreTemp := score,
		@rank := @rank + 1 
	FROM
		t_stu_score,
		( SELECT @scoreTemp := NULL, @rank := 1, @rankInit := 0 ) r 
	ORDER BY class_id ASC,score DESC
	) temp;

分组后每组取前两条（可用️）

SELECT temp.class_id, temp.stu_id, temp.score, temp.rank 
FROM
	(
	SELECT
	 	t_stu_score.*,
		IF(@classIdTemp = class_id, CASE WHEN @scoreTemp = score THEN @Rank  WHEN @scoreTemp := score THEN @rank := @rank + 1 END, @rank := 1 ) AS rank,
		@classIdTemp := class_id,
		@scoreTemp := score
	FROM
		t_stu_score,
		( SELECT @classIdTemp := NULL, @scoreTemp := NULL ) r 
	ORDER BY class_id ASC,score DESC
	) temp where temp.rank <= 2

来源：https://www.cnblogs.com/niniya/p/9046449.html

分组后每组取前两条（页面存在分页条件可用。）

( select class_id, stu_id, score from t_stu_score where class_id = 1 order by score  DESC limit 2 )
union all
( select class_id, stu_id, score from t_stu_score where class_id = 2 order by score  DESC limit 2 )
union all
( select class_id, stu_id, score from t_stu_score where class_id = 3 order by score  DESC limit 2 )

分组后每组取前两条(数据不重复可用。排名重复数据会有问题)

select temp.* from (
	SELECT
		t1.class_id, t1.stu_id, t1.score,
		(
		SELECT count( 1 ) FROM t_stu_score t2 
		WHERE t2.class_id = t1.class_id  AND t2.score >= t1.score 
		) top 
	FROM
		t_stu_score t1 order by t1.class_id ASC,top ASC
)temp where temp.top <= 2;

分组后每组取前两条(排名重复数据会有问题)

select 	t1.class_id, t1.stu_id, t1.score
from t_stu_score t1
where (SELECT count( 1 ) FROM t_stu_score t2  WHERE t2.class_id = t1.class_id  AND t2.score >= t1.score ) <= 2
order by t1.class_id ASC

来源：https://cloud.tencent.com/developer/article/1488593