SQL 強化練習 (六)

靈活用 left join, group by + having 和結合子查詢, 早日達到熟練程度吧.

本以為學會了Python 就已經天下無敵, 果然, 我還是太傻太天真了. 業務中幾乎就沒有用 Python 來直接連接配接資料庫進行操作, 當然我是說資料這塊哈. 哎, 難受, 還是用的 sql 這種方式. 但有個問題在于, sql 沒有類似于程式設計語言那樣來用個資料結構存儲存儲中間過程, 于是呢, 在寫 "套娃" 就是 sql 嵌套的時候, 可難受了, 一不小心就會寫亂, 閱讀體驗也不好, 但, 又沒有其他的辦法, 隻能去多加練習去适應哦.

表關系

反複練習, 隻能這樣去提高了解了, 果然是唯手熟爾, 對此我深信不疑.

需求 01

-- 查詢 沒有學全所有課程的學生學号, 姓名

分析

student 表 left join score 表, 然後在 group by s_id 再 having 選課數量 < 實際有多少門課

-- 先看看總體情況
select 
  st.*,
  sc.*

from student as st 
left join score as sc 
  on st.s_id = sc.s_id      

tips: 一定要用 left join 這樣才不會漏掉有學生壓根就沒有選過課的情況

+------+-----------+------------+--------+------+------+-------+
| s_id | s_name    | birth_date | gender | s_id | c_id | score |
+------+-----------+------------+--------+------+------+-------+
| 0001 | 王二      | 1989-01-01 | 男     | 0001 | 0001 |    80 |
| 0001 | 王二      | 1989-01-01 | 男     | 0001 | 0002 |    90 |
| 0001 | 王二      | 1989-01-01 | 男     | 0001 | 0003 |    99 |
| 0002 | 星落      | 1990-12-21 | 女     | 0002 | 0002 |    60 |
| 0002 | 星落      | 1990-12-21 | 女     | 0002 | 0003 |    80 |
| 0003 | 胡小适    | 1991-12-21 | 男     | 0003 | 0001 |    80 |
| 0003 | 胡小适    | 1991-12-21 | 男     | 0003 | 0002 |    80 |
| 0003 | 胡小适    | 1991-12-21 | 男     | 0003 | 0003 |    80 |
| 0004 | 油哥      | 1996-10-01 | 男     | NULL | NULL |  NULL |
+------+-----------+------------+--------+------+------+-------+      

果然, left join 就能把 "油哥" 這個兄弟, 一門課都不選的特例, 也給篩選出來了哦.

然後, "沒有選全" , 也就是 group by 學生id, count(課程号) < 總課程數 了呗. 組内篩選用 having.

select
  st.*, 
  sc.*
from student as st 
left join score as sc  on st.s_id = sc.s_id

group by st.s_id having
  count(distinct sc.c_id) < (select count(distinct c_id) from course);      

+------+--------+------------+--------+------+------+-------+
| s_id | s_name | birth_date | gender | s_id | c_id | score |
+------+--------+------------+--------+------+------+-------+
| 0002 | 星落   | 1990-12-21 | 女     | 0002 | 0003 |    80 |
| 0004 | 油哥   | 1996-10-01 | 男     | NULL | NULL |  NULL |
+------+--------+------------+--------+------+------+-------+      

這樣不就, 擷取到了 s_id 了嗎, 然後在外面給套上一層, 就搞定了哦.

select 
  s_id as "學号", 
  s_name as "姓名"

from student where s_id in (

  select
    st.s_id  -- 隻要學号即可
  from student as st 
  left join score as sc  on st.s_id = sc.s_id

  group by st.s_id having
    count(distinct sc.c_id) < (select count(distinct c_id) from course)

  );      

+--------+--------+
| 學号   | 姓名   |
+--------+--------+
| 0002   | 星落   |
| 0004   | 油哥   |
+--------+--------+
2 rows in set (0.00 sec)      

需求 02

查詢 至少有一門課, 與學号為 "0001" 的學生, 所學課程相同, 的學生學号和姓名.

分析

先看 0001 這個老鐵選了哪些課, 然後從 score 中查出 選個這個課有哪些學生學号 不就行了嘛

select c_id from score where s_id = "0001";       

+------+
| c_id |
+------+
| 0001 |
| 0002 |
| 0003 |
+------+      

這個兄弟, 把 1,2,3 号課程都給選上了, 果然是學霸, 目測.

然後再看, score 中 也選擇了這些課的學号有哪些.

select
  s_id,
  c_id

from score 
where c_id in (

  select c_id from score where s_id = "0001"

);      

+------+------+
| s_id | c_id |
+------+------+
| 0001 | 0001 |
| 0003 | 0001 |
| 0001 | 0002 |
| 0002 | 0002 |
| 0003 | 0002 |
| 0001 | 0003 |
| 0002 | 0003 |
| 0003 | 0003 |
+------+------+
8 rows in set (0.00 sec)      

這樣就把跟 0001 同學有選相關的課程的所有兄弟的, 學号, 課号給 拉出來了. 然後, 需要再對學号進行去重, 同時呢, 還需要将 0001 自己給排除掉哦

select 
  s_id as "學号", 
  s_name as "姓名"

from student where s_id in (

  select distinct s_id from score 
  where c_id in (

  select c_id from score where s_id = "0001"
  )
 -- 排除自己 
and s_id != "0001"

);      

+--------+-----------+
| 學号   | 姓名      |
+--------+-----------+
| 0002   | 星落      |
| 0003   | 胡小适    |
+--------+-----------+
2 rows in set (0.00 sec)
      

當然, 也可以用 inner join 的方式, 當資料量比較大的時候, 感覺用 join 的方式會更加快一點哦

select 
  a.s_id as "學号", 
  a.s_name as "姓名"
  from student as a 

-- 内連接配接查出的學号
inner join (

  select distinct s_id from score 
  where c_id in (

  select c_id from score where s_id = "0001"
  )
   -- 排除自己 
  and s_id != "0001") as b

on a.s_id = b.s_id;      

+--------+-----------+
| 學号   | 姓名      |
+--------+-----------+
| 0002   | 星落      |
| 0003   | 胡小适    |
+--------+-----------+
2 rows in set (0.00 sec)
      

小結

• 靈活應用 join, group by + having 這樣的子查詢的方式, "面向過程" 寫sql, 就是一點點查出來.
• 當考慮資料量的時候, 如果先用的子查詢, 在查詢效率上, 可能也應多考慮 join 來進行配合使用
• 感覺sql 其實和面向過程的程式設計是一樣的, 查一個表就取個别名, 然後繼續查, 拼接, 或者套娃啥的, 感覺熟練了就會好還多的, 還是需要不斷練習哦.