SQL筆記：在一張包含學生ID、課程、成績的表中，檢索各科前三名

表定義 及 測試數據

<code> 
CREATE
 
TABLE
 
`score_info`
 (

  
`student_id`
 
int
 
NOT
 
NULL
 AUTO_INCREMENT,

  
`category`
 
varchar
(
255
) 
DEFAULT
 
NULL
,

  
`score`
 
int
 
DEFAULT
 
NULL
,

  PRIMARY 
KEY
 (
`student_id`
)

) 
ENGINE
=
InnoDB
 AUTO_INCREMENT=
24
 
DEFAULT
 
CHARSET
=utf8mb4 
COLLATE
=utf8mb4_0900_ai_ci;

INSERT
  
INTO
 
`score_info`
 
VALUES
 (
'1'
, 
'語'
, 
'99'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'2'
, 
'數'
, 
'32'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'3'
, 
'外'
, 
'88'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'4'
,  
'語'
, 
'89'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'5'
, 
'數'
, 
'57'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'6'
, 
'外'
, 
'67'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'7'
, 
'語'
, 
'72'
);

INSERT
 
INTO
  
`score_info`
 
VALUES
 (
'8'
, 
'數'
, 
'48'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'9'
, 
'外'
, 
'53'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'10'
, 
'語'
, 
'96'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'11'
, 
'數'
,  
'69'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'12'
, 
'數'
, 
'39'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'13'
, 
'外'
, 
'93'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'14'
, 
'數'
, 
'39'
);

INSERT
 
INTO
 
`score_info`
  
VALUES
 (
'15'
, 
'外'
, 
'93'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'16'
, 
'語'
, 
'96'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'17'
, 
'數'
, 
'39'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'18'
, 
'語'
, 
'96'
);

 
INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'19'
, 
'數'
, 
'88'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'20'
, 
'語'
, 
'77'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'21'
, 
'數'
, 
'54'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 ( 
'22'
, 
'外'
, 
'73'
);

INSERT
 
INTO
 
`score_info`
 
VALUES
 (
'23'
, 
'數'
, 
'96'
);/<code>

查詢結果

• 根據分數排名（分數相同 按先後排序）

<code>
SELECT
	t.student_id,
	t.category,
	t.score,
	t.rowNumber
FROM
	(
		
SELECT
			s.*,@rowNumber := (
				
CASE
				
WHEN
 @categoryName = s.category 
THEN
					@rowNumber + 
1
				
ELSE
					
1
				 
END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := 
category
 categoryNameAfter
		
FROM
			score_info s,
			(
				
SELECT
					@rowNumber := 
0
 ,@categoryName := 
'--'
			) r
		
ORDER
 
BY
			
category
 
DESC
,
			score 
DESC
	) t
WHERE
	t.rowNumber <= 
3
/<code>

• 根據分數排民（分數相同並列）

<code>
SELECT
	t.category,
	t.score,
	t.studentIds,
	t.rowNumber
FROM
	(
		
SELECT
			s.category,
			s.score,
			
GROUP_CONCAT
(s.student_id) studentIds ,@rowNumber := (
				
CASE
				
WHEN
 @categoryName = s.category 
THEN
					@rowNumber + 
1
				
ELSE
					
1
				
END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := 
category
 categoryNameAfter
		
FROM
			score_info s,
			(
				
SELECT
					@rowNumber := 
0
 ,@categoryName := 
'--'
			) r
		
GROUP
 
BY
			
category
,
			score
		
ORDER
  
BY
			
category
 
DESC
,
			score 
DESC
	) t
WHERE
	t.rowNumber <= 
3
/<code>

思路

要在一張包含學生ID、成績、課程的表中檢索出每門課程的前三名

1）、分組課程，再按成績排序

<code>
SELECT
	s.*,@rowNumber :=@rowNumber + 
1
 rowNumber
FROM
	score_info s,
	(
SELECT
 @rowNumber := 
0
) r
ORDER
 
BY
	
category
 
DESC
,
	score 
DESC
;/<code>

2）、要根據不同的課程各自排序，要先獲取到“上一條數據”的課程類型

<code>
SELECT
	s.*,@rowNumber :=@rowNumber + 
1
 rowNumber ,@categoryName categoryNameBefor ,@categoryName := 
category
 categoryNameAfter
FROM
	score_info s,
	(
		
SELECT
			@rowNumber := 
0
 ,@categoryName := 
'--'
	) r
ORDER
 
BY
	
category
 
DESC
,
	score 
DESC
;/<code>

3）、已經獲取到上一條數據的課程類型，那就可以用當前課程比對上一條課程，判斷是否重新排序

<code>
SELECT
	s.*,@rowNumber := (
		
CASE
		
WHEN
 @categoryName = s.category 
THEN
			@rowNumber + 
1
		
ELSE
			
1
		
END
	) rowNumber ,@categoryName categoryNameBefor ,@categoryName := 
category
 categoryNameAfter
FROM
	score_info s,
	(
		
SELECT
			@rowNumber := 
0
 ,@categoryName := 
'--'
	) r
ORDER
 
BY
	
category
 
DESC
,
	score 
DESC
;/<code>

4）、分組課程和成績，實現 課程相同 且 成績相同的排名並列（根據需求 自行決定是否需要並列）

<code>
SELECT
			s.category,
			s.score,
			
GROUP_CONCAT
(s.student_id) studentIds ,@rowNumber := (
				
CASE
				
WHEN
 @categoryName = s.category 
THEN
					@rowNumber + 
1
				
ELSE
					
1
				
END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := 
category
 categoryNameAfter
		
FROM
			score_info s,
			(
				
SELECT
					@rowNumber := 
0
 ,@categoryName := 
'--'
			) r
		
GROUP
 
BY
			
category
,
			score
		
ORDER
 
BY
			
category
 
DESC 
,
			score 
DESC
/<code>

5）、已經可以獲取到各科成績排名，要獲取前三名 可以直接where rowNumber<=3 ，即可實現檢索各科前三名

<code>
SELECT
	t.category,
	t.score,
	t.studentIds,
	t.rowNumber
FROM
	(
		
SELECT
			s.category,
			s.score,
			
GROUP_CONCAT
(s.student_id) studentIds ,@rowNumber := (
				 
CASE
				
WHEN
 @categoryName = s.category 
THEN
					@rowNumber + 
1
				
ELSE
					
1
				
END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := 
category
 categoryNameAfter
		
FROM
			score_info s,
			(
				
SELECT
					@rowNumber := 
0
 ,@categoryName := 
'--'
			) r
		
GROUP
 
BY
			
category
,
			score
		
ORDER
 
BY
			
category
 
DESC
,
			score 
DESC
	) t
WHERE
	t.rowNumber <= 
3
/<code>