先預測結果，再看輸出。
SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。
01｜先理解資料與結果
WITH name AS(查詢) 給中間結果命名，後續查詢可將它當成表參照。每個階段先確認一列代表什麼：原紀錄、配對、區域或日期；粒度錯誤會讓後續總和重複計入。CTE 提升可讀性，但不保證被實體儲存或依撰寫順序執行，最佳化器仍可合併或重排。
02｜建立正確查詢規則
資料模型用主鍵識別一列，用外鍵維持表間參照，避免把只是同區域的多個測站誤當成一個唯一項目。索引可縮小搜尋或排序成本，但也占空間並增加寫入維護；應用 EXPLAIN 檢查計畫，而非假定每查詢都用索引。交易把多個步驟作為可提交或回復的單位，隔離層級與鎖則控制並行觀察。
簡單範例｜正值區域摘要
先保留正 reading，再依 zone 輸出正讀值筆數及總和；無正值區域不輸出，NULL zone 同組。
先閱讀下方 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"]]}, {"name": "stations", "columns": [{"name": "station_id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "target", "type": "INTEGER", "nullable": true}, {"name": "enabled", "type": "INTEGER", "nullable": true}], "rows": [[8, "west", 0, null], [2, "north", 9, 1], [9, "", -2, 1], [11, null, 3, 1], [3, "south", null, 0], [14, "east", 1, 0], [5, "north", 5, 1]]}, {"name": "left_samples", "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": [[3, "north", 5, 2, 1, 2, "alpha"], [4, "west", 5, -2, 1, 2, "ab"], [11, "south", 9, 2, 1, 20, "atlas"], [9, "", 3, 3, 1, 8, ""], [8, "north", 9, 1, null, 8, "omega"], [7, "south", -2, 0, 0, 4, "a"], [12, "east", 1, 0, null, 30, "beta"], [1, null, null, null, null, 1, null], [6, null, null, 4, 1, 4, "gamma"], [5, "east", -5, 5, 0, 3, "beta"], [10, "west", 0, null, 0, 15, "delta"], [2, "north", 0, 0, 0, 1, ""]]}, {"name": "right_samples", "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": [[9, "", 3, 3, 1, 8, ""], [10, "west", 0, null, 0, 15, "delta"], [12, "east", 1, 0, null, 30, "beta"], [7, "south", -2, 0, 0, 4, "a"], [21, "north", 5, 3, 1, 10, "a"], [3, "north", 5, 2, 1, 2, "alpha"], [8, "north", 9, 1, null, 8, "omega"], [5, "east", -5, 5, 0, 3, "beta"], [1, null, null, null, null, 1, null], [2, "north", 0, 0, 0, 1, ""], [20, "north", 5, 3, 1, 10, "a"]]}]}SQLite
WITH positive_rows AS (SELECT zone,reading FROM readings WHERE reading>0)
SELECT zone AS zone, row_count AS row_count, total AS total
FROM (
SELECT zone AS zone, COUNT(*) AS row_count, SUM(reading) AS total
FROM positive_rows
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE BINARY ASC;PostgreSQL
WITH positive_rows AS (SELECT zone,reading FROM readings WHERE reading>0)
SELECT zone AS zone, row_count AS row_count, total AS total
FROM (
SELECT zone AS zone, COUNT(*) AS row_count, SUM(reading) AS total
FROM positive_rows
GROUP BY zone
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE "C" ASC;MySQL
WITH positive_rows AS (SELECT zone,reading FROM readings WHERE reading>0)
SELECT zone AS zone, row_count AS row_count, total AS total
FROM (
SELECT zone AS zone, COUNT(*) AS row_count, SUM(reading) AS total
FROM positive_rows
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 | total "" | 1 | 3 "east" | 1 | 1 "north" | 2 | 14 "south" | 1 | 9 "west" | 1 | 5
中等範例｜每日筆數變化
每個有紀錄 day 的所有列數，減去前一個有紀錄 day 的列數；首日 change 為 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"]]}, {"name": "stations", "columns": [{"name": "station_id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "target", "type": "INTEGER", "nullable": true}, {"name": "enabled", "type": "INTEGER", "nullable": true}], "rows": [[8, "west", 0, null], [2, "north", 9, 1], [9, "", -2, 1], [11, null, 3, 1], [3, "south", null, 0], [14, "east", 1, 0], [5, "north", 5, 1]]}, {"name": "left_samples", "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": [[3, "north", 5, 2, 1, 2, "alpha"], [4, "west", 5, -2, 1, 2, "ab"], [11, "south", 9, 2, 1, 20, "atlas"], [9, "", 3, 3, 1, 8, ""], [8, "north", 9, 1, null, 8, "omega"], [7, "south", -2, 0, 0, 4, "a"], [12, "east", 1, 0, null, 30, "beta"], [1, null, null, null, null, 1, null], [6, null, null, 4, 1, 4, "gamma"], [5, "east", -5, 5, 0, 3, "beta"], [10, "west", 0, null, 0, 15, "delta"], [2, "north", 0, 0, 0, 1, ""]]}, {"name": "right_samples", "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": [[9, "", 3, 3, 1, 8, ""], [10, "west", 0, null, 0, 15, "delta"], [12, "east", 1, 0, null, 30, "beta"], [7, "south", -2, 0, 0, 4, "a"], [21, "north", 5, 3, 1, 10, "a"], [3, "north", 5, 2, 1, 2, "alpha"], [8, "north", 9, 1, null, 8, "omega"], [5, "east", -5, 5, 0, 3, "beta"], [1, null, null, null, null, 1, null], [2, "north", 0, 0, 0, 1, ""], [20, "north", 5, 3, 1, 10, "a"]]}]}SQLite
WITH daily AS (SELECT day,COUNT(*) AS row_count FROM readings GROUP BY day)
SELECT day AS day, row_count AS row_count, change AS change
FROM (
SELECT day AS day, row_count AS row_count, row_count-LAG(row_count) OVER (ORDER BY day) AS change
FROM daily
) query_result
ORDER BY day ASC;PostgreSQL
WITH daily AS (SELECT day,COUNT(*) AS row_count FROM readings GROUP BY day)
SELECT day AS day, row_count AS row_count, change AS change
FROM (
SELECT day AS day, row_count AS row_count, row_count-LAG(row_count) OVER (ORDER BY day) AS change
FROM daily
) query_result
ORDER BY day ASC;MySQL
WITH daily AS (SELECT day,COUNT(*) AS row_count FROM readings GROUP BY day)
SELECT day AS day, row_count AS row_count, `change` AS `change`
FROM (
SELECT day AS day, row_count AS row_count, row_count-LAG(row_count) OVER (ORDER BY day) AS `change`
FROM daily
) query_result
ORDER BY day ASC;預期查詢結果
day | row_count | change 1 | 2 | NULL 2 | 2 | 0 3 | 1 | -1 4 | 2 | 1 8 | 2 | 0 15 | 1 | -1 20 | 1 | 0 30 | 1 | 0
困難範例｜標記連續區段
按 id 升序，首列為區段 1；flag 與前列不同則區段號加 1，兩個 NULL 視為相同，一個 NULL 與已知值視為不同。輸出 id、flag、區段號。
先閱讀下方 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"]]}, {"name": "stations", "columns": [{"name": "station_id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "target", "type": "INTEGER", "nullable": true}, {"name": "enabled", "type": "INTEGER", "nullable": true}], "rows": [[8, "west", 0, null], [2, "north", 9, 1], [9, "", -2, 1], [11, null, 3, 1], [3, "south", null, 0], [14, "east", 1, 0], [5, "north", 5, 1]]}, {"name": "left_samples", "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": [[3, "north", 5, 2, 1, 2, "alpha"], [4, "west", 5, -2, 1, 2, "ab"], [11, "south", 9, 2, 1, 20, "atlas"], [9, "", 3, 3, 1, 8, ""], [8, "north", 9, 1, null, 8, "omega"], [7, "south", -2, 0, 0, 4, "a"], [12, "east", 1, 0, null, 30, "beta"], [1, null, null, null, null, 1, null], [6, null, null, 4, 1, 4, "gamma"], [5, "east", -5, 5, 0, 3, "beta"], [10, "west", 0, null, 0, 15, "delta"], [2, "north", 0, 0, 0, 1, ""]]}, {"name": "right_samples", "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": [[9, "", 3, 3, 1, 8, ""], [10, "west", 0, null, 0, 15, "delta"], [12, "east", 1, 0, null, 30, "beta"], [7, "south", -2, 0, 0, 4, "a"], [21, "north", 5, 3, 1, 10, "a"], [3, "north", 5, 2, 1, 2, "alpha"], [8, "north", 9, 1, null, 8, "omega"], [5, "east", -5, 5, 0, 3, "beta"], [1, null, null, null, null, 1, null], [2, "north", 0, 0, 0, 1, ""], [20, "north", 5, 3, 1, 10, "a"]]}]}SQLite
WITH prior AS (SELECT id,flag,LAG(id) OVER (ORDER BY id) AS previous_id,LAG(flag) OVER (ORDER BY id) AS previous_flag FROM readings), markers AS (SELECT id,flag,CASE WHEN previous_id IS NULL OR (flag IS NULL AND previous_flag IS NOT NULL) OR (flag IS NOT NULL AND previous_flag IS NULL) OR flag<>previous_flag THEN 1 ELSE 0 END AS new_segment FROM prior)
SELECT id AS id, flag AS flag, segment_no AS segment_no
FROM (
SELECT id AS id, flag AS flag, SUM(new_segment) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS segment_no
FROM markers
) query_result
ORDER BY id ASC;PostgreSQL
WITH prior AS (SELECT id,flag,LAG(id) OVER (ORDER BY id) AS previous_id,LAG(flag) OVER (ORDER BY id) AS previous_flag FROM readings), markers AS (SELECT id,flag,CASE WHEN previous_id IS NULL OR (flag IS NULL AND previous_flag IS NOT NULL) OR (flag IS NOT NULL AND previous_flag IS NULL) OR flag<>previous_flag THEN 1 ELSE 0 END AS new_segment FROM prior)
SELECT id AS id, flag AS flag, segment_no AS segment_no
FROM (
SELECT id AS id, flag AS flag, SUM(new_segment) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS segment_no
FROM markers
) query_result
ORDER BY id ASC;MySQL
WITH prior AS (SELECT id,flag,LAG(id) OVER (ORDER BY id) AS previous_id,LAG(flag) OVER (ORDER BY id) AS previous_flag FROM readings), markers AS (SELECT id,flag,CASE WHEN previous_id IS NULL OR (flag IS NULL AND previous_flag IS NOT NULL) OR (flag IS NOT NULL AND previous_flag IS NULL) OR flag<>previous_flag THEN 1 ELSE 0 END AS new_segment FROM prior)
SELECT id AS id, flag AS flag, segment_no AS segment_no
FROM (
SELECT id AS id, flag AS flag, SUM(new_segment) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS segment_no
FROM markers
) query_result
ORDER BY id ASC;預期查詢結果
id | flag | segment_no 1 | NULL | 1 2 | 0 | 2 3 | 1 | 3 4 | 1 | 3 5 | 0 | 4 6 | 1 | 5 7 | 0 | 6 8 | NULL | 7 9 | 1 | 8 10 | 0 | 9 11 | 1 | 10 12 | NULL | 11
06｜本機索引與查詢計畫
索引可讓資料庫較快找到符合條件的列，但不改變查詢的正確結果，也不代替 ORDER BY。以下本機範例建立 zone 索引；之後可在 SQLite 用 EXPLAIN QUERY PLAN、PostgreSQL／MySQL 用 EXPLAIN 查看 SELECT。計畫依版本、統計與資料量改變，小表未用索引也可能合理。
SQLite
CREATE TABLE scratch_search (id INTEGER NOT NULL PRIMARY KEY,zone VARCHAR(20),reading INTEGER);
INSERT INTO scratch_search(id,zone,reading) VALUES (1,'north',3),(2,'west',5),(3,'north',7);
CREATE INDEX scratch_zone_index ON scratch_search(zone);
SELECT id AS id,reading AS reading FROM scratch_search WHERE zone='north' ORDER BY id;PostgreSQL
CREATE TABLE scratch_search (id INTEGER NOT NULL PRIMARY KEY,zone VARCHAR(20),reading INTEGER);
INSERT INTO scratch_search(id,zone,reading) VALUES (1,'north',3),(2,'west',5),(3,'north',7);
CREATE INDEX scratch_zone_index ON scratch_search(zone);
SELECT id AS id,reading AS reading FROM scratch_search WHERE zone='north' ORDER BY id;MySQL
CREATE TABLE scratch_search (id INTEGER NOT NULL PRIMARY KEY,zone VARCHAR(20),reading INTEGER);
INSERT INTO scratch_search(id,zone,reading) VALUES (1,'north',3),(2,'west',5),(3,'north',7);
CREATE INDEX scratch_zone_index ON scratch_search(zone);
SELECT id AS id,reading AS reading FROM scratch_search WHERE zone='north' ORDER BY id;預期查詢結果
id | reading 1 | 3 3 | 7
07｜本機交易與回復
本機交易在 BEGIN／START TRANSACTION 與 COMMIT／ROLLBACK 之間執行。此例先建表並寫入初始資料，再開始交易，修改兩列後 ROLLBACK，所以查詢仍得到 3、7。COMMIT 才保留此次修改。交易原子性不等於所有並行觀察都一致：隔離層級、鎖與重試需另行設計；MySQL 的某些 DDL 會隱含提交，所以本例把建表放在交易前。
SQLite
CREATE TABLE scratch_transaction (id INTEGER NOT NULL PRIMARY KEY,reading INTEGER);
INSERT INTO scratch_transaction(id,reading) VALUES (1,3),(2,7);
BEGIN;
UPDATE scratch_transaction SET reading=30 WHERE id=1;
UPDATE scratch_transaction SET reading=70 WHERE id=2;
ROLLBACK;
SELECT id AS id,reading AS reading FROM scratch_transaction ORDER BY id;PostgreSQL
CREATE TABLE scratch_transaction (id INTEGER NOT NULL PRIMARY KEY,reading INTEGER);
INSERT INTO scratch_transaction(id,reading) VALUES (1,3),(2,7);
BEGIN;
UPDATE scratch_transaction SET reading=30 WHERE id=1;
UPDATE scratch_transaction SET reading=70 WHERE id=2;
ROLLBACK;
SELECT id AS id,reading AS reading FROM scratch_transaction ORDER BY id;MySQL
CREATE TABLE scratch_transaction (id INTEGER NOT NULL PRIMARY KEY,reading INTEGER);
INSERT INTO scratch_transaction(id,reading) VALUES (1,3),(2,7);
START TRANSACTION;
UPDATE scratch_transaction SET reading=30 WHERE id=1;
UPDATE scratch_transaction SET reading=70 WHERE id=2;
ROLLBACK;
SELECT id AS id,reading AS reading FROM scratch_transaction ORDER BY id;預期查詢結果
id | reading 1 | 3 2 | 7
WHEN SOMETHING GOES WRONG
遇到錯誤，先檢查這裡
每階段粒度不一致
註明每列代表什麼，再核對 JOIN 是否會複製中間摘要。
把 CTE 當成必然效能優化
看實際查詢計畫及資料規模，不以 WITH 多少判斷速度。
在題庫提交寫入或交易命令
題庫只接受唯讀 SELECT／WITH；建表、索引與交易範例在本機獨立練習庫操作。
PAUSE AND TRY
先不要急著看別人的寫法
- 說明每日先彙總再累計，與逐筆累計的結果列代表什麼。
- 用兩個 NULL flag 連續出現測試區段分割，為何不應新增區段？
- 在本機對同個查詢建立索引前後執行 EXPLAIN，哪些計畫變化可觀察？
先改一列資料，預測查詢結果，再切換引擎確認語法與排序。
把這幾件事帶走
- 用 CTE 描述中間結果
- 核對各階段列數與 NULL 規則
- 理解鍵、索引與交易在真實資料庫的用途
FROM READING TO PRACTICE
用三道免費題，練習本章觀念
從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。
延伸閱讀：資料庫官方文件
需要查閱更完整的規則時，可參考以下官方文件。