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"课程成绩高的课程分数

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表查询出学生信息，可以直接连，也可以使用临时表连接。

# 直接添加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 ;

或者

#使用临时表连接，这种方法虽然复杂，但是能减少连接表数据量，会快一些。
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.计算出所有学生的每个课程的成绩

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表就能出来。
注意使用内连接，因为只展示公共部分。

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