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

背景

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

题目描述

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

• 相关题目:
  1、查询"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

解题思路

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

第一种思路：自连接

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.*,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