SQL 運算與 CASE

先預測結果，再看輸出。

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

01｜先理解資料與結果

SELECT 可為每列計算表達式。一般算術遇到 NULL 會得到 NULL；reading+weight 不會自動把缺值當成 0。COALESCE 取第一個非 NULL 值，NULLIF(a,b) 在兩值相等時回傳 NULL。CASE WHEN 條件 THEN 值 ELSE 值 END 依序挑第一個為真的分支。

02｜建立正確查詢規則

不同引擎的整數除法與小數精度規則不同。本課以 1.0*reading/weight 明確要求小數結果，並用 CASE 或 NULLIF 保護分母。對數學比例，負數或大於 1 不一定是錯誤；要看資料是否帶方向。平台的浮點容許差為 max(1e-9,1e-9×max(|實際值|,|預期值|))，不要擅自四捨五入。

簡單範例｜反向讀值

輸出 -reading。

先閱讀下方 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, value AS value
FROM (
SELECT id AS id, -reading AS value
FROM readings
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, -reading AS value
FROM readings
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, -reading AS value
FROM readings
) query_result
ORDER BY id ASC;

預期查詢結果

id | value
1 | NULL
2 | 0
3 | -5
4 | -5
5 | 5
6 | NULL
7 | 2
8 | -9
9 | -3
10 | 0
11 | -9
12 | -1

中等範例｜正部訊號

reading 為 NULL 保持 NULL；正數保留，其餘補 0。

先閱讀下方 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, value AS value
FROM (
SELECT id AS id, CASE WHEN reading IS NULL THEN NULL WHEN reading>0 THEN reading ELSE 0 END AS value
FROM readings
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, CASE WHEN reading IS NULL THEN NULL WHEN reading>0 THEN reading ELSE 0 END AS value
FROM readings
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, CASE WHEN reading IS NULL THEN NULL WHEN reading>0 THEN reading ELSE 0 END AS value
FROM readings
) query_result
ORDER BY id ASC;

預期查詢結果

id | value
1 | NULL
2 | 0
3 | 5
4 | 5
5 | 0
6 | NULL
7 | 0
8 | 9
9 | 3
10 | 0
11 | 9
12 | 1

困難範例｜訊號方向

正讀值輸出 1、負讀值 -1、零 0、NULL 保持 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 id AS id, value AS value
FROM (
SELECT id AS id, CASE WHEN reading IS NULL THEN NULL WHEN reading>0 THEN 1 WHEN reading<0 THEN -1 ELSE 0 END AS value
FROM readings
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, CASE WHEN reading IS NULL THEN NULL WHEN reading>0 THEN 1 WHEN reading<0 THEN -1 ELSE 0 END AS value
FROM readings
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, CASE WHEN reading IS NULL THEN NULL WHEN reading>0 THEN 1 WHEN reading<0 THEN -1 ELSE 0 END AS value
FROM readings
) query_result
ORDER BY id ASC;

預期查詢結果

id | value
1 | NULL
2 | 0
3 | 1
4 | 1
5 | -1
6 | NULL
7 | -1
8 | 1
9 | 1
10 | 0
11 | 1
12 | 1

06｜本機寫入與修改

以下 DML 僅在本機獨立練習庫執行。INSERT 增加列，UPDATE 修改符合 WHERE 的列，DELETE 刪除符合條件的列。漏掉 WHERE 的 UPDATE／DELETE 會影響全表，先用相同 WHERE 的 SELECT 預覽範圍。這個範例先把 id=1 的讀值加 1，再刪掉缺測列，最後查詢確認。

SQLite

CREATE TABLE scratch_changes (id INTEGER NOT NULL PRIMARY KEY,reading INTEGER);
INSERT INTO scratch_changes(id,reading) VALUES (1,3),(2,NULL),(3,-2);
UPDATE scratch_changes SET reading=reading+1 WHERE id=1;
DELETE FROM scratch_changes WHERE reading IS NULL;
SELECT id AS id,reading AS reading FROM scratch_changes ORDER BY id;

PostgreSQL

CREATE TABLE scratch_changes (id INTEGER NOT NULL PRIMARY KEY,reading INTEGER);
INSERT INTO scratch_changes(id,reading) VALUES (1,3),(2,NULL),(3,-2);
UPDATE scratch_changes SET reading=reading+1 WHERE id=1;
DELETE FROM scratch_changes WHERE reading IS NULL;
SELECT id AS id,reading AS reading FROM scratch_changes ORDER BY id;

MySQL

CREATE TABLE scratch_changes (id INTEGER NOT NULL PRIMARY KEY,reading INTEGER);
INSERT INTO scratch_changes(id,reading) VALUES (1,3),(2,NULL),(3,-2);
UPDATE scratch_changes SET reading=reading+1 WHERE id=1;
DELETE FROM scratch_changes WHERE reading IS NULL;
SELECT id AS id,reading AS reading FROM scratch_changes ORDER BY id;

預期查詢結果

id | reading
1 | 4
3 | -2

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

把 0 與 NULL 混為同一種缺值

只有題意要求忽略 0 時才使用 NULLIF(value,0)。

忘記 ELSE 分支

沒有條件為真且沒有 ELSE 時會得到 NULL；確認這是否符合題意。

先做除法再補值

先處理分母為 0 或缺值，再計算比值。

PAUSE AND TRY

先不要急著看別人的寫法

reading=0、weight=5 時，COALESCE 與非零備援各選什麼？
對 -4、4 的雙值，最小值與最接近零的平手規則是否相同？
修改一列 weight 為 0，安全比值應為何？

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

把這幾件事帶走

用 CASE 明寫分支
區分 COALESCE 與 NULLIF
避免整數除法與零分母造成錯誤結果

FROM READING TO PRACTICE

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

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

簡單

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

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

01｜先理解資料與結果

02｜建立正確查詢規則

簡單範例｜反向讀值

中等範例｜正部訊號

困難範例｜訊號方向

06｜本機寫入與修改

遇到錯誤，先檢查這裡

先不要急著看別人的寫法

把這幾件事帶走

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

076. 反向讀值

084. 正部訊號

093. 訊號方向

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