MySQL50-11-第41-45題
本文中介紹的是第41-45題，主要包含的知識點：
- 表的自連接查詢比較信息
- 找出前2名
- 分組排序+having 過濾
5個題目是
- 查詢不同課程成績相同的學生的學生編號、課程編號车海、學生成績
- 每門功課最好的前2名
- 統(tǒng)計每門課程的學生選修人數(shù)（超過5人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)昌跌，查詢結(jié)果按人數(shù)降序排列帕棉，若人數(shù)相同，按課程號升序排列
- 檢索至少選修兩門課程的學生學號
- 查詢選修了全部課程的學生信息
image
題目41
題目需求
查詢不同課程成績相同的學生的學生編號书幕、課程編號、學生成績
分析過程
- 課程成績：Score揽趾，s_score
- 學生編號：Score台汇，s_id
- 課程編號：Score，c_id
3個字段同時在一個表中篱瞎，所以我們可以通過一個表Score的自連接來實現(xiàn)查出
SQL實現(xiàn)
select
a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;
image-20201122001411784
我們對學號還需要去重下：
select
distinct a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;
image
再看看原始的數(shù)據(jù)中是否符合要求：
image
題目42
題目需求
查詢每門功成績最好的前兩名
分析過程
題目的要求就是找出每門課的前2名同學
- 成績：Score
- 學科：Course
SQL實現(xiàn)
自己的方法
還需要好好優(yōu)化的??
-- 先找出語文的前2名同學
select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '語文' -- 改成數(shù)學和英語即可求出相應(yīng)的信息
order by sc.s_score desc
limit 2;
image
將3門學科的信息進行拼接即可求出答案：
-- 最終腳本
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '語文'
order by sc.s_score desc
limit 2)
union
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '數(shù)學'
order by sc.s_score desc
limit 2)
union
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '英語'
order by sc.s_score desc
limit 2)
image
參考方法（好方法）
如何解決前幾名排序的問題??????太牛了
select
a.c_id
,a.s_id
,a.s_score
from Score a
where (select count(1) -- count(1)類似count(*)：統(tǒng)計表b中分數(shù)大的數(shù)量
from Score b
where b.c_id=a.c_id -- 課程相同
and b.s_score >= a.s_score) <= 2 -- 前2名
order by a.c_id;
image
首先我們看看真實的數(shù)據(jù)苟呐，我們以01課程來進行解釋上面的代碼：
image
符合要求count(1)<=2的只有兩種情況
image
還需要好好理解下??
題目43
image
題目需求
統(tǒng)計每門課程的學生選修人數(shù)（超過5人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)俐筋，查詢結(jié)果按人數(shù)降序排列牵素，若人數(shù)相同，按課程號升序排列
分析過程
課程號：Score澄者，c_id
學生：Score笆呆，s_id
SQL實現(xiàn)
select
c_id
,count(s_score) num
from Score
group by c_id
having num > 5
order by num desc, c_id;
image
題目44
題目需求
檢索至少選修兩門課程的學生學號
分析過程
課程：Score请琳，c_id
學號：Score，s_id
SQL實現(xiàn)
結(jié)果顯示全部滿足要求
select
s_id
,count(*) num
from Score
group by s_id
having num >= 2;
image
題目45
題目需求
查詢選修了全部課程的學生信息
分析過程
- 首先我們在Course表看看全部課程數(shù)目是多少(num)
- 然后在Score表中找出課程是num的學生信息
SQL實現(xiàn)
自己的方法
1腰奋、全部的課程數(shù)目num
select count(*) from Course; -- 總共3門
image-20201122095253954
2单起、從Score表分組統(tǒng)計每個人的課程數(shù)目，滿足是3的學生信息
select
s_id
,count(c_id) num -- 課程數(shù)目
from Score
group by s_id
having num in (select count(*)
from Course); --滿足全部課程
image
3劣坊、我們找出上面結(jié)果中的學生信息即可
select
s.*
,count(c_id) num -- 課程數(shù)目
from Score sc
join Student s
on sc.s_id = s.s_id
group by s.s_id
having num in (select count(*)
from Course); --滿足全部課程
image
參考方法
select * -- 3嘀倒、s_id對應(yīng)的學生信息
from Student
where s_id in(select s_id -- 2、最大課程數(shù)對應(yīng)的s_id
from Score
group by s_id
having count(*)=(select count(*) from Course) -- 1局冰、全部課程數(shù)
)
