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

背景

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

题目描述

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

答案

s_id s_name s_brith s_sex score_01 score_02
01 赵雷 1990-01-01 男 80 90
05 周梅 1991-12-01 女 76 87

解题思路

这个题目两种解题思路和是一样的，只需要修改对应的 > 为 < 即可。

第一种思路：自连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 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
 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