先預測結果，再看輸出。
SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。
01｜先理解資料與結果
ORDER BY 由左至右比較排序鍵，前鍵平手才使用下一鍵。想得到固定名單，需要能區分不同結果列的最後鍵，例如唯一 id。SQL 引擎的預設 NULL 排位不同：本課用 CASE WHEN reading IS NULL THEN 1 ELSE 0 END 先把缺值放後面，再比較 reading。
02｜建立正確查詢規則
LIMIT 3 是排序後最多三列，不保證一定有三列；OFFSET 3 先跳過前三列。若只有兩列，OFFSET 3 後為空。分頁必須使用一致且完整的 ORDER BY。資料在兩次查詢之間改變時，即使排序固定，OFFSET 分頁仍可能重複或漏列；真實系統可考慮以唯一排序鍵做游標分頁。
簡單範例｜讀值升序
輸出所有原始欄位。
先閱讀下方 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 id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC, id ASC;PostgreSQL
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC, id ASC;MySQL
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC, id ASC;預期查詢結果
id | zone | reading | weight | flag | day | label 5 | "east" | -5 | 5 | 0 | 3 | "beta" 7 | "south" | -2 | 0 | 0 | 4 | "a" 2 | "north" | 0 | 0 | 0 | 1 | "" 10 | "west" | 0 | NULL | 0 | 15 | "delta" 12 | "east" | 1 | 0 | NULL | 30 | "beta" 9 | "" | 3 | 3 | 1 | 8 | "" 3 | "north" | 5 | 2 | 1 | 2 | "alpha" 4 | "west" | 5 | -2 | 1 | 2 | "ab" 8 | "north" | 9 | 1 | NULL | 8 | "omega" 11 | "south" | 9 | 2 | 1 | 20 | "atlas" 1 | NULL | NULL | NULL | NULL | 1 | NULL 6 | NULL | NULL | 4 | 1 | 4 | "gamma"
中等範例｜日期倒序
輸出所有原始欄位。
先閱讀下方 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 id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY day DESC, id ASC;PostgreSQL
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY day DESC, id ASC;MySQL
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY day DESC, id ASC;預期查詢結果
id | zone | reading | weight | flag | day | label 12 | "east" | 1 | 0 | NULL | 30 | "beta" 11 | "south" | 9 | 2 | 1 | 20 | "atlas" 10 | "west" | 0 | NULL | 0 | 15 | "delta" 8 | "north" | 9 | 1 | NULL | 8 | "omega" 9 | "" | 3 | 3 | 1 | 8 | "" 6 | NULL | NULL | 4 | 1 | 4 | "gamma" 7 | "south" | -2 | 0 | 0 | 4 | "a" 5 | "east" | -5 | 5 | 0 | 3 | "beta" 3 | "north" | 5 | 2 | 1 | 2 | "alpha" 4 | "west" | 5 | -2 | 1 | 2 | "ab" 1 | NULL | NULL | NULL | NULL | 1 | NULL 2 | "north" | 0 | 0 | 0 | 1 | ""
困難範例｜第二頁測量
輸出所有原始欄位。排序後套用 LIMIT 3 OFFSET 3；不足所需列數時僅回傳存在的列，超過末尾則為空表。
先閱讀下方 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 id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC, id ASC
LIMIT 3 OFFSET 3;PostgreSQL
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC, id ASC
LIMIT 3 OFFSET 3;MySQL
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC, id ASC
LIMIT 3 OFFSET 3;預期查詢結果
id | zone | reading | weight | flag | day | label 10 | "west" | 0 | NULL | 0 | 15 | "delta" 12 | "east" | 1 | 0 | NULL | 30 | "beta" 9 | "" | 3 | 3 | 1 | 8 | ""
WHEN SOMETHING GOES WRONG
遇到錯誤，先檢查這裡
只按有平手的 reading 排序
加上 id 等唯一鍵；同值不同紀錄也要有固定順序。
把 NULL 當成數值 0 排序
缺值排位是獨立規則，不應用 COALESCE 偷換資料值。
先截取再排序
外層 ORDER BY 與 LIMIT 的階段要與題意一致，不能先任取三列。
PAUSE AND TRY
先不要急著看別人的寫法
- 新增相同 reading、較小 id 的列，前 3 名如何改變？
- 比較 reading DESC NULL 最後與 NULL 最前的名單。
- 分頁不足三列時，是否應補 NULL 列？為什麼？
先改一列資料，預測查詢結果，再切換引擎確認語法與排序。
把這幾件事帶走
- 指定 ASC／DESC 與平手鍵
- 跨引擎明寫 NULL 排位
- 先排序再 LIMIT／OFFSET
FROM READING TO PRACTICE
用三道免費題，練習本章觀念
從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。
延伸閱讀：資料庫官方文件
需要查閱更完整的規則時，可參考以下官方文件。