CodeSprout程式萌芽

CHAPTER 06 · DATABASE FOUNDATIONS

SQL 分組與 HAVING

建立分組統計，先篩列再篩組，避免混入不屬於該組的資料。

這一章學會
  • 列出完整 GROUP BY 鍵
  • 區分 WHERE 與 HAVING
  • 理解 NULL 分組鍵與空來源
先預測結果，再看輸出。

SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。

01｜先理解資料與結果

GROUP BY 按鍵組合把原列分到各組；相同位置的 NULL 分組鍵歸為同一組。SELECT 中非聚合欄位應包含在 GROUP BY，避免從組內任意挑值。空來源沒有任何組，所以分組聚合與整體聚合的空表列數不同。

02｜建立正確查詢規則

WHERE 在分組前篩列，HAVING 在聚合後篩組。先 WHERE reading>0 再 COUNT(*) 只建立含正值的組；對所有列分組再 SUM(CASE...) 則仍可出現正值數為 0 的組。分組輸出也沒有自然順序，需額外 ORDER BY。

簡單範例｜區域筆數

依 zone 分組，輸出分組欄位及每組所有列數；同一分組欄位的 NULL 歸為同組，空來源不產生任何組。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}
SQLite
SELECT zone AS zone, row_count AS row_count
FROM (
SELECT zone AS zone, COUNT(*) AS row_count
FROM readings
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE BINARY ASC;
PostgreSQL
SELECT zone AS zone, row_count AS row_count
FROM (
SELECT zone AS zone, COUNT(*) AS row_count
FROM readings
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE "C" ASC;
MySQL
SELECT zone AS zone, row_count AS row_count
FROM (
SELECT zone AS zone, COUNT(*) AS row_count
FROM readings
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE utf8mb4_bin ASC;
預期查詢結果
zone | row_count
"" | 1
"east" | 2
"north" | 3
"south" | 2
"west" | 2
NULL | 2

中等範例｜區域權重和

依 zone 分組，輸出分組欄位及每組非 NULL weight 總和，無有效值為 0；同一分組欄位的 NULL 歸為同組，空來源不產生任何組。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}
SQLite
SELECT zone AS zone, total_weight AS total_weight
FROM (
SELECT zone AS zone, COALESCE(SUM(weight),0) AS total_weight
FROM readings
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE BINARY ASC;
PostgreSQL
SELECT zone AS zone, total_weight AS total_weight
FROM (
SELECT zone AS zone, COALESCE(SUM(weight),0) AS total_weight
FROM readings
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE "C" ASC;
MySQL
SELECT zone AS zone, total_weight AS total_weight
FROM (
SELECT zone AS zone, COALESCE(SUM(weight),0) AS total_weight
FROM readings
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE utf8mb4_bin ASC;
預期查詢結果
zone | total_weight
"" | 3
"east" | 5
"north" | 3
"south" | 2
"west" | -2
NULL | 4

困難範例｜全缺測區域

依 zone 分組，輸出分組欄位及每組列數，只保留有效 reading 筆數為 0 的組；同一分組欄位的 NULL 歸為同組，空來源不產生任何組。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}
SQLite
SELECT zone AS zone, row_count AS row_count
FROM (
SELECT zone AS zone, COUNT(*) AS row_count
FROM readings
GROUP BY zone
HAVING COUNT(reading)=0
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE BINARY ASC;
PostgreSQL
SELECT zone AS zone, row_count AS row_count
FROM (
SELECT zone AS zone, COUNT(*) AS row_count
FROM readings
GROUP BY zone
HAVING COUNT(reading)=0
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE "C" ASC;
MySQL
SELECT zone AS zone, row_count AS row_count
FROM (
SELECT zone AS zone, COUNT(*) AS row_count
FROM readings
GROUP BY zone
HAVING COUNT(reading)=0
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE utf8mb4_bin ASC;
預期查詢結果
zone | row_count
NULL | 2

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

將聚合條件放在 WHERE

SUM(reading)>0 是組的條件，應放 HAVING。

選取不在分組中的欄位

只選分組鍵與聚合，或先明確定義要選哪一列。

以 = NULL 尋找 NULL 組

NULL 分組存在，但後續比對仍需 IS NULL 或明寫缺值安全相等。

PAUSE AND TRY

先不要急著看別人的寫法

  1. 同 zone 有兩筆 NULL reading，COUNT(*) 與 COUNT(reading) 如何不同？
  2. 比較先篩正值與先分組後計正值的區域清單。
  3. 新增不同 day 的同 zone 記錄，zone 與 zone-day 分組各增加多少組？

先改一列資料，預測查詢結果，再切換引擎確認語法與排序。

把這幾件事帶走

  • 列出完整 GROUP BY 鍵
  • 區分 WHERE 與 HAVING
  • 理解 NULL 分組鍵與空來源

FROM READING TO PRACTICE

用三道免費題，練習本章觀念

從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。

簡單

126. 區域筆數

開始練習 中等

134. 區域權重和

開始練習 困難

143. 全缺測區域

開始練習

延伸閱讀：資料庫官方文件

需要查閱更完整的規則時，可參考以下官方文件。