MySQL SELECT 聯集查詢

標籤：合并   color   ble   ber   sql   實現   desc   查詢   union   

這裡主要分享一下聯集查詢的用法。

MySQL使用UNION和UNION ALL實現資料的聯集查詢。

假設有以下兩個表：

mysql> select * from staff;+----+----------+-------+| id | name     | slary |+----+----------+-------+|  1 | guoding  |  3200 ||  2 | dingtao  |  2800 ||  3 | haofugui |  3500 ||  4 | guoming  |  4000 ||  5 | haotian  |  2900 ||  6 | fengfei  |  3200 ||  7 | guoting  |  2600 |+----+----------+-------+7 rows in set (0.00 sec)mysql> select * from staff_1;+----+----------+-------+| id | name     | slary |+----+----------+-------+|  1 | guoding  |  3200 ||  2 | liding   |  2700 ||  3 | haofugui |  3500 ||  4 | xiaoli   |  3600 ||  5 | yazhi    |  3200 ||  6 | yuanfei  |  3200 ||  7 | guoting  |  3500 |+----+----------+-------+7 rows in set (0.00 sec)

　mysql> select * from staff_2;
　+----+-------+----------+
　| id | slary | name |
　+----+-------+----------+
　| 1 | 3200 | guoding |
　| 2 | 2700 | liding |
　| 3 | 3500 | haofugui |
　| 4 | 3600 | xiaoli |
　| 5 | 3200 | yazhi |
　| 6 | 3200 | yuanfei |
　| 7 | 3500 | guoting |
　+----+-------+----------+
　7 rows in set (0.00 sec)

 

　mysql> select * from staff_3;
　+----------+-------+
　| name | slary |
　+----------+-------+
　| guoding | 3200 |
　| liding | 2700 |
　| haofugui | 3500 |
　| xiaoli | 3600 |
　+----------+-------+
　4 rows in set (0.00 sec)

 

1. UNION

語句：SELECT column_name FROM table1 UNION SELECT column_name FROM table2

說明：1）用於合并兩個或多個 SELECT 語句的結果集，並消去表中任何重複行；
             2）UNION 內部的 SELECT 語句必須擁有相同數量的列，列也必須擁有相似的資料類型；
             3）同時，每條 SELECT 語句中的列的順序必須相同；

             4）如果子句中有order by,limit等，需用括弧()包起來，推薦放到所有子句之後，即對最終合并的結果來排序或篩選。

樣本：

 

mysql> select * from staff union select * from staff_1;+----+----------+-------+| id | name     | slary |+----+----------+-------+|  1 | guoding  |  3200 ||  2 | dingtao  |  2800 ||  3 | haofugui |  3500 ||  4 | guoming  |  4000 ||  5 | haotian  |  2900 ||  6 | fengfei  |  3200 ||  7 | guoting  |  2600 ||  2 | liding   |  2700 ||  4 | xiaoli   |  3600 ||  5 | yazhi    |  3200 ||  6 | yuanfei  |  3200 ||  7 | guoting  |  3500 |+----+----------+-------+12 rows in set (0.00 sec)mysql> select * from staff union select * from staff_2;+----+----------------------+----------------------+| id | name                 | slary                |+----+----------------------+----------------------+|  1 | guoding              | 3200                 ||  2 | dingtao              | 2800                 ||  3 | haofugui             | 3500                 ||  4 | guoming              | 4000                 ||  5 | haotian              | 2900                 ||  6 | fengfei              | 3200                 ||  7 | guoting              | 2600                 ||  1 | 3200                 | guoding              ||  2 | 2700                 | liding               ||  3 | 3500                 | haofugui             ||  4 | 3600                 | xiaoli               ||  5 | 3200                 | yazhi                ||  6 | 3200                 | yuanfei              ||  7 | 3500                 | guoting              |+----+----------------------+----------------------+14 rows in set (0.00 sec)mysql> select * from staff union select name from staff_1;ERROR 1222 (21000): The used SELECT statements have a different number of columnsmysql> select * from staff union select * from staff_3;ERROR 1222 (21000): The used SELECT statements have a different number of columns

mysql> (select * from staff order by id asc) union (select * from staff_1 order by slary desc);+----+----------+-------+| id | name     | slary |+----+----------+-------+|  1 | guoding  |  3200 ||  2 | dingtao  |  2800 ||  3 | haofugui |  3500 ||  4 | guoming  |  4000 ||  5 | haotian  |  2900 ||  6 | fengfei  |  3200 ||  7 | guoting  |  2600 ||  2 | liding   |  2700 ||  4 | xiaoli   |  3600 ||  5 | yazhi    |  3200 ||  6 | yuanfei  |  3200 ||  7 | guoting  |  3500 |+----+----------+-------+12 rows in set (0.00 sec)

 

2. UNION ALL

作用及規則與UNION相同，區別是，UNION ALL不消除重複行

樣本：

mysql> select * from staff union select * from staff_1;+----+----------+-------+| id | name     | slary |+----+----------+-------+|  1 | guoding  |  3200 ||  2 | dingtao  |  2800 ||  3 | haofugui |  3500 ||  4 | guoming  |  4000 ||  5 | haotian  |  2900 ||  6 | fengfei  |  3200 ||  7 | guoting  |  2600 ||  2 | liding   |  2700 ||  4 | xiaoli   |  3600 ||  5 | yazhi    |  3200 ||  6 | yuanfei  |  3200 ||  7 | guoting  |  3500 |+----+----------+-------+12 rows in set (0.00 sec)mysql> select * from staff union all select * from staff_1;+----+----------+-------+| id | name     | slary |+----+----------+-------+|  1 | guoding  |  3200 ||  2 | dingtao  |  2800 ||  3 | haofugui |  3500 ||  4 | guoming  |  4000 ||  5 | haotian  |  2900 ||  6 | fengfei  |  3200 ||  7 | guoting  |  2600 ||  1 | guoding  |  3200 ||  2 | liding   |  2700 ||  3 | haofugui |  3500 ||  4 | xiaoli   |  3600 ||  5 | yazhi    |  3200 ||  6 | yuanfei  |  3200 ||  7 | guoting  |  3500 |+----+----------+-------+14 rows in set (0.00 sec)

 

MySQL SELECT 聯集查詢