MySQL UNION 合并結(jié)果集

UNION 用于將多個 SELECT 語句的結(jié)果合并到結(jié)果集中犁跪。第一個 SELECT 語句的列名被用作返回結(jié)果的列列名炒瘸。在每個 SELECT 語句的相應(yīng)位置列出的選定列應(yīng)該有相同的數(shù)據(jù)類型牛哺。

測試數(shù)據(jù)和表結(jié)構(gòu)

mysql> desc books;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255)        | NO   |     | NULL    |                |
| create_time | datetime            | NO   |     | NULL    |                |
| is_hot      | tinyint(3) unsigned | NO   |     | 0       |                |
+-------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from books;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java                         | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code                            | 2017-12-01 17:19:36 |      0 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
8 rows in set (0.00 sec)

組合查詢

where 和 union 語句在多數(shù)情況下可以實現(xiàn)相同的結(jié)果集讼渊。where 可以實現(xiàn)的語句棍潘，一定可以用 union 實現(xiàn)瓶籽。而 union 可以實現(xiàn)的語句匠童，where 卻不一定能做到。因為塑顺，union 是可以針對多張表進(jìn)行結(jié)果集的合并汤求。
查詢 id = 1 的記錄

mysql> select * from books where id = 1;
+----+-----------------+---------------------+--------+
| id | name            | create_time         | is_hot |
+----+-----------------+---------------------+--------+
|  1 | Head First Java | 2017-12-04 17:19:36 |      0 |
+----+-----------------+---------------------+--------+
1 row in set (0.00 sec)

查詢 is_hot = 1 的記錄

mysql> select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)

使用 union 合并兩個查詢

mysql> select * from books where id = 1 union select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
4 rows in set (0.00 sec)

當(dāng)然，我們通過 select * from books where id = 1 or is_hot = 1 也可以做到严拒，但是 union 是不限于表的扬绪。也就是說，我們可以從多個表中查詢結(jié)果集出來糙俗，然后 merge 到第一個查詢語句查詢的結(jié)果集中（前提是字段數(shù)量和類型一致）勒奇。

重復(fù)項

默認(rèn)情況下，我們單單使用 union 語句巧骚，會自動幫我們?nèi)コ貜?fù)的項赊颠。但是，如果我們使用了 union all 語句劈彪，數(shù)據(jù)庫不會幫我們自動去除重復(fù)項竣蹦。

mysql> select * from books where id > 5 union select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
4 rows in set (0.00 sec)

mysql> select * from books where id > 5 union all select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
6 rows in set (0.00 sec)

關(guān)于排序

union 中的排序是比較坑的，即使我們在部分結(jié)果集這種寫了 order by 語句沧奴。因為痘括，union 會在合并結(jié)果集之后自動的進(jìn)行排序。意味著在子結(jié)果集中的排序滔吠，排了也是白排纲菌。例如：

mysql> (select * from books where is_hot = 1 order by create_time desc)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc);
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java                         | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code                            | 2017-12-01 17:19:36 |      0 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
+----+---------------------------------------+---------------------+--------+
8 rows in set (0.00 sec)

從查詢的意圖來看，我們是想讓 is_hot = 1 和 is_hot = 0 的結(jié)果集各自按 create_time 字段進(jìn)行排序疮绷。但是結(jié)果出乎意外翰舌，并沒有按我們設(shè)定的順序進(jìn)行。
其實冬骚，子結(jié)果集是進(jìn)行了排序的椅贱，只不過 union 在合并結(jié)果集之后，會自動的對整個結(jié)果集進(jìn)行排序只冻。
假如我們現(xiàn)在有一個需求庇麦，is_hot = 1 的是熱門書單，is_hot = 0 的是一般書單喜德，然后按熱門書單按 create_time 排序山橄，普通書單也按 create_time 排序，返回一個集合住诸〖莸ǎ或者分頁查詢涣澡。我們該如何實現(xiàn)這個需求呢？查詢語句如下：

mysql> (select * from books where is_hot = 1 order by create_time desc limit 7)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc limit 7);
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java                         | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code                            | 2017-12-01 17:19:36 |      0 |
+----+---------------------------------------+---------------------+--------+
8 rows in set (0.00 sec)

在每一個查詢語句的后面都寫上 limit 7 (總條數(shù))丧诺。那么分頁呢入桂？

mysql> (select id, name, is_hot, create_time from books where is_hot = 1 order by create_time desc limit 7)
    -> union
    -> (select id, name, is_hot, create_time from books where is_hot = 0 order by create_time desc limit 7)
    -> limit 3 offset 0;
+----+---------------------------------------+--------+---------------------+
| id | name                                  | is_hot | create_time         |
+----+---------------------------------------+--------+---------------------+
|  8 | Pro Spring Boot                       |      1 | 2017-12-09 17:19:36 |
|  6 | Java 9 Data Structures and Algorithms |      1 | 2017-12-07 17:19:36 |
|  4 | The Ruby Programming                  |      1 | 2017-12-05 17:19:36 |
+----+---------------------------------------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> (select id, name, is_hot, create_time from books where is_hot = 1 order by create_time desc limit 7)
    -> union
    -> (select id, name, is_hot, create_time from books where is_hot = 0 order by create_time desc limit 7)
    -> limit 3 offset 3;
+----+------------------------------+--------+---------------------+
| id | name                         | is_hot | create_time         |
+----+------------------------------+--------+---------------------+
|  7 | The C Programming Language   |      0 | 2017-12-08 17:19:36 |
|  5 | JavaScript Definitive Guides |      0 | 2017-12-06 17:19:36 |
|  1 | Head First Java              |      0 | 2017-12-04 17:19:36 |
+----+------------------------------+--------+---------------------+
3 rows in set (0.00 sec)

多表查詢

創(chuàng)建一張雜志表 magazines 并插入兩條數(shù)據(jù)。

mysql> desc magazines;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255)        | NO   |     | NULL    |                |
| create_time | datetime            | NO   |     | NULL    |                |
| is_hot      | tinyint(2) unsigned | NO   |     | 0       |                |
+-------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from magazines;
+----+--------------+---------------------+--------+
| id | name         | create_time         | is_hot |
+----+--------------+---------------------+--------+
|  1 | 男人裝        | 2017-12-05 09:28:06 |      0 |
|  2 | 程序員        | 2017-12-01 09:28:06 |      1 |
+----+--------------+---------------------+--------+
3 rows in set (0.00 sec)

假如需求是從兩張表或者多張表中查詢出結(jié)果集驳阎，并且分頁顯示抗愁。那么這時候 where 很顯然做不到，但是 union 卻可以輕松做到呵晚。例如：查詢 books 和 magazines 表中 is_hot = 1 的數(shù)據(jù)蜘腌。

mysql> select * from books where is_hot = 1
    -> union
    -> select * from magazines where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  2 | 程序員                                 | 2017-12-01 09:28:06 |      1 |
+----+---------------------------------------+---------------------+--------+
4 rows in set (0.01 sec)

區(qū)分多表

聯(lián)合多張表查詢的時候，有時候會需要做到區(qū)分?jǐn)?shù)據(jù)來自于哪一張表饵隙。比如撮珠，查詢熱門雜志和熱門書籍，并且分別顯示來自于哪一張表金矛。

mysql> select *, 'books' as table_name from books where is_hot = 1
    -> union
    -> select *, 'magazines' as table_name from magazines where is_hot = 1;
+----+---------------------------------------+---------------------+--------+------------+
| id | name                                  | create_time         | is_hot | table_name |
+----+---------------------------------------+---------------------+--------+------------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 | books      |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 | books      |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 | books      |
|  2 | 程序員                                 | 2017-12-01 09:28:06 |      1 | magazines  |
+----+---------------------------------------+---------------------+--------+------------+
4 rows in set (0.00 sec)

這樣查詢出來的結(jié)果集芯急，封裝成對象集合返回給前端，也方便前端對 books 或者 magazines 打不同的 tag驶俊。

具體應(yīng)用場景

比如我們要對 books 表中的數(shù)據(jù)進(jìn)行分頁顯示娶耍，要求熱門書籍在上面，普通書籍在下面饼酿。熱門書籍按 create_time 倒序榕酒，普通書籍也按 create_time 倒序。然后分頁故俐，每頁顯示 5 條數(shù)據(jù)想鹰。
第一頁的數(shù)據(jù)：

mysql> (select * from books where is_hot = 1 order by create_time desc limit 8)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc limit 8)
    -> limit 5 offset 0;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
+----+---------------------------------------+---------------------+--------+
5 rows in set (0.01 sec)

第二頁的數(shù)據(jù)：

mysql> (select * from books where is_hot = 1 order by create_time desc limit 8)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc limit 8)
    -> limit 5 offset 5;
+----+-----------------+---------------------+--------+
| id | name            | create_time         | is_hot |
+----+-----------------+---------------------+--------+
|  1 | Head First Java | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java   | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code      | 2017-12-01 17:19:36 |      0 |
+----+-----------------+---------------------+--------+
3 rows in set (0.00 sec)

參考資料

https://dev.mysql.com/doc/refman/5.7/en/any-in-some-subqueries.html

最后編輯于：2017.12.05 22:37:52

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市药版，隨后出現(xiàn)的幾起案子杖挣，更是在濱河造成了極大的恐慌，老刑警劉巖刚陡，帶你破解...
沈念sama閱讀 211,194評論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異株汉，居然都是意外死亡筐乳，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,058評論 2贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門乔妈，熙熙樓的掌柜王于貴愁眉苦臉地迎上來蝙云，“玉大人，你說我怎么就攤上這事路召〔伲” “怎么了波材？”我有些...
開封第一講書人閱讀 156,780評論 0贊 346
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長身隐。經(jīng)常有香客問我廷区，道長，這世上最難降的妖魔是什么贾铝？我笑而不...
開封第一講書人閱讀 56,388評論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任隙轻，我火速辦了婚禮，結(jié)果婚禮上垢揩，老公的妹妹穿的比我還像新娘玖绿。我一直安慰自己，他們只是感情好叁巨，可當(dāng)我...
茶點(diǎn)故事閱讀 65,430評論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布斑匪。她就那樣靜靜地躺著，像睡著了一般锋勺。火紅的嫁衣襯著肌膚如雪蚀瘸。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,764評論 1贊 290
城市分裂傳說
那天宙刘，我揣著相機(jī)與錄音苍姜，去河邊找鬼。笑死悬包，一個胖子當(dāng)著我的面吹牛衙猪，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播布近，決...
沈念sama閱讀 38,907評論 3贊 406
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼垫释，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了撑瞧？” 一聲冷哼從身側(cè)響起棵譬，我...
開封第一講書人閱讀 37,679評論 0贊 266
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎预伺，沒想到半個月后订咸，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 44,122評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡酬诀，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,459評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年脏嚷，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片瞒御。...
茶點(diǎn)故事閱讀 38,605評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡父叙，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情趾唱，我是刑警寧澤涌乳，帶...
沈念sama閱讀 34,270評論 4贊 329
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站甜癞，受9級特大地震影響夕晓，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜带欢，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,867評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一运授、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧乔煞，春花似錦吁朦、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,734評論 0贊 21
一樁弒父案逗宜，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至空骚，卻和暖如春纺讲，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背囤屹。一陣腳步聲響...
開封第一講書人閱讀 31,961評論 1贊 265
情欲美人皮
我被黑心中介騙來泰國打工熬甚，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人肋坚。一個月前我還...
沈念sama閱讀 46,297評論 2贊 360
代替公主和親
正文我出身青樓乡括，卻偏偏與公主長得像，于是被迫代替她去往敵國和親智厌。傳聞我的和親對象是個殘疾皇子诲泌，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,472評論 2贊 348