侧边栏壁纸
博主头像
小鱼吃猫博客博主等级

你所热爱的,便是你的生活。

  • 累计撰写 114 篇文章
  • 累计创建 47 个标签
  • 累计收到 9 条评论

目 录CONTENT

文章目录

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

小鱼吃猫
2023-11-02 / 0 评论 / 0 点赞 / 33 阅读 / 3170 字

背景

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

题目描述

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

答案

s_ids_names_briths_sexscore_01score_02
02钱电1990-12-217060
04李云1990-08-065030

解题思路

第一种方案:自连接查询

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_idscore_01score_02score_03
01809099
02706080
03808080
04503020
057687null
0631null34
07null8998
  • 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
0

评论区