萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL UNION 與 UNION ALL 實戰:去重、排序、分頁、置頂邏輯

核心語法

SELECT ... 
UNION [ALL | DISTINCT] 
SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
  • UNION = UNION DISTINCT:結果集去重(隱式排序+唯一化),較慢
  • UNION ALL:直接合併,**不去重、不排序**,效能高得多
  • 各 SELECT 列數、類型、順序必須一致;結果集列名取自第一個 SELECT

全局 ORDER BY / LIMIT

需用括號包裹各子查詢,ORDER BY/LIMIT 放最後:

(SELECT a FROM t WHERE a=10)
UNION
(SELECT a FROM t WHERE a=11)
ORDER BY a LIMIT 10;

實戰場景一:置頂文章 + 普通文章分頁

(SELECT * FROM `blog` WHERE top=1 ORDER BY created DESC)
UNION
(SELECT * FROM `blog` WHERE top=0 ORDER BY created DESC)
LIMIT 2, 3;

優雅實現「置頂優先、其次按時間」的單查詢分頁。

實戰場景二:子查詢排序後再 UNION

SELECT * FROM (
    SELECT * FROM t1 ORDER BY score DESC
    UNION ALL
    SELECT * FROM t2 ORDER BY score DESC
) AS combined
ORDER BY score DESC;

排序欄位別名陷阱

-- 正確:ORDER BY 別名
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;

-- 錯誤:ORDER BY 原列名
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;  -- Unknown column 'a'

單獨子查詢排序

(SELECT a FROM t WHERE a=10 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t WHERE a=11 ORDER BY a LIMIT 10);

雙向排序需求:前 3 點擊最高,其餘按時間

SELECT *, 1 AS ord FROM women WHERE status=2 AND (title LIKE '%2014%' OR title LIKE '%秋冬%')
UNION
SELECT *, 2 AS ord FROM women WHERE status=2 AND (keywords LIKE '%2014%' OR keywords LIKE '%秋冬%')
ORDER BY ord, id DESC LIMIT 0,15;

用常數列 ord 標記來源優先級,再二次排序。

效能建議

  • 確無重複數據時**必用 UNION ALL**
  • 大結果集 UNION 會用到臨時表+文件排序,慎用
  • 可改寫為 WHERE ... OR ... 或應用層合併
copyright © 萬盛學電腦網 all rights reserved