解決 MySQL UNION 子句無法直接使用 ORDER BY 的方法

MySQL UNION 與 ORDER BY 衝突的根因與解決方案

在 MySQL 中，UNION 合併多個 SELECT 時，若直接在子查詢後加 ORDER BY，會報錯 Incorrect usage of UNION and ORDER BY。本文通過實例逐步演示如何繞過限制，取得預期排序結果。

問題重現

需求：查詢 ID 大於某值的下一條、小於某值的上一條，合併輸出。

-- 錯誤寫法
SELECT id, title FROM subjects WHERE id > 100 ORDER BY id LIMIT 1
UNION
SELECT id, title FROM subjects WHERE id < 100 ORDER BY id LIMIT 1;

MySQL 規定：UNION 的各子查詢不能單獨帶 ORDER BY ... LIMIT，除非包裹在子查詢中。

解決步驟

1. 用括號包裹第一個子查詢，使其成為衍生表：

SELECT * FROM (
    SELECT id, title FROM subjects WHERE id > 100 ORDER BY id LIMIT 1
) AS t1
UNION
SELECT id, title FROM subjects WHERE id < 100 ORDER BY id LIMIT 1;

但 MySQL 要求衍生表必須有別名，否則報 Every derived table must have its own alias。

2. 為衍生表加上別名（如 t1），即可成功執行。

SELECT * FROM (
    SELECT id, title FROM subjects WHERE id > 100 ORDER BY id LIMIT 1
) AS t1
UNION
SELECT id, title FROM subjects WHERE id < 100 ORDER BY id LIMIT 1;

原理解析

• UNION 預設會去重並排序（隱含 ORDER BY），因此禁止子查詢單獨排序以免語意衝突。
• 將子查詢包裹成衍生表（SELECT * FROM (...) AS alias），MySQL 會先執行內層查詢（含 ORDER BY LIMIT），再將結果集作為臨時表參與 UNION，繞過限制。
• 第二個子查詢不加括號也能帶 ORDER BY LIMIT，因為它是 UNION 的最後一個分支，MySQL 允許最後一個分支排序（但建議統一包裹以保持對稱）。

最佳實踐：統一包裹 + 外層排序

SELECT * FROM (
    SELECT id, title FROM subjects WHERE id > 100 ORDER BY id LIMIT 1
) AS t1
UNION ALL
SELECT * FROM (
    SELECT id, title FROM subjects WHERE id < 100 ORDER BY id DESC LIMIT 1
) AS t2
ORDER BY id;  -- 外層統一排序

• 用 UNION ALL 避免去重開銷（業務上 ID 唯一）。
• 外層再加一次 ORDER BY 確保最終輸出順序。

適用版本與替代方案

MySQL 5.7+、8.0 均適用。若頻繁需要此類「鄰近記錄」查詢，建議改用窗口函數（MySQL 8.0）：

SELECT id, title FROM (
    SELECT id, title,
           LAG(id) OVER (ORDER BY id) AS prev_id,
           LEAD(id) OVER (ORDER BY id) AS next_id
    FROM subjects
) t
WHERE id = 100 OR prev_id = 100 OR next_id = 100;