MySQL练习题50道：查询”01”课程比”02”课程成绩高的学生的信息及课程分数

背景

MySQL经典练习题及答案，常用SQL语句练习50题

题目描述

1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数

答案

s_id s_name s_brith s_sex score_01 score_02
02 钱电 1990-12-21 男 70 60
04 李云 1990-08-06 男 50 30

解题思路

第一种方案：自连接查询

score表自己连接自己查询，分别取出课程01和02的成绩，并且score_01>score_02

  • Step1.查询出”01”课程比”02”课程成绩高的课程分数
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	a.c_id,
	a.s_score AS score_01,
	b.s_score AS score_02 
FROM
	score a,
	score b
WHERE
	a.s_id = b.s_id 
	AND a.c_id = '01' 
	AND b.c_id = '02' 
	AND a.s_score > b.s_score
  • Step2.连接Student表查询出学生信息，可以直接连，也可以使用临时表连接。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 直接添加Student表进行连接查询
SELECT
	s.*,
	a.s_score AS score_01,
	b.s_score AS score_02 
FROM
	score a,
	score b,
	student s 
WHERE
	a.s_id = b.s_id 
	AND a.c_id = '01' 
	AND b.c_id = '02' 
	AND a.s_score > b.s_score 
	AND s.s_id = a.s_id ;

或者

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#使用临时表连接，这种方法虽然复杂，但是能减少连接表数据量，会快一些。
SELECT
	stu.*,
	tmp.score_01,
	tmp.score_02 
FROM
	(
	SELECT
		a.s_id,
		a.s_score AS score_01,
		b.s_score AS score_02 
	FROM
		score a,
		score b 
	WHERE
		a.s_id = b.s_id 
		AND a.c_id = '01' 
		AND b.c_id = '02' 
		AND a.s_score > b.s_score 
	) tmp
	LEFT JOIN student stu ON stu.s_id = tmp.s_id;

第二种方案：长表变宽表

计算出所有学生的每个课程的成绩，然后取出”01”课程比”02”课程成绩高

  • Step1.计算出所有学生的每个课程的成绩
1
2
3
4
5
6
7
8
9
SELECT
	s_id,
	max( CASE WHEN c_id = '01' THEN s_score END ) AS score_01,
	max( CASE WHEN c_id = '02' THEN s_score END ) AS score_02,
	max( CASE WHEN c_id = '03' THEN s_score END ) AS score_03 
FROM
	score 
GROUP BY
	s_id

上述SQL会将score表合成一个宽表，包含每个学生的各科成绩，数据如下。

s_id score_01 score_02 score_03
01 80 90 99
02 70 60 80
03 80 80 80
04 50 30 20
05 76 87 null
06 31 null 34
07 null 89 98
  • Step2.取出”01”课程比”02”课程成绩高的学生信息，这一步直接添加where条件就可以。

把第一步中的结果加上score_01>score_02，然后联合Student表就能出来。
注意使用内连接，因为只展示公共部分。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT stu.*,sc.score_01,sc.score_02
FROM student stu INNER JOIN
(SELECT
	sc.s_id,
	max( CASE WHEN sc.c_id = '01' THEN s_score END ) AS score_01,
	max( CASE WHEN sc.c_id = '02' THEN s_score END ) AS score_02,
	max( CASE WHEN sc.c_id = '03' THEN s_score END ) AS score_03 
FROM
	score sc 
GROUP BY
	s_id) sc
	ON stu.s_id=sc.s_id
	AND sc.score_01>sc.score_02