MySQL資料庫（20）：聯合查詢 union

聯合查詢

1、應用場景

1. 同一張表中不同結果合并到一起展示：男生升高升序，女生升高降序
2. 資料量較大的表，進行分表操作，将每張表的資料合并起來顯示

2、基本文法

select 語句
union [union 選項]
select 語句;
           

• distinct 去重，預設
• all 儲存所有結果

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 劉備   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 張飛   |        2 |   21 |      1 |
|  8 | 關羽   |        1 |   22 |      2 |
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+

-- 預設選項：distinct
select * from my_student
union
select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 劉備   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 張飛   |        2 |   21 |      1 |
|  8 | 關羽   |        1 |   22 |      2 |
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+


select * from my_student
union all
select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 劉備   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 張飛   |        2 |   21 |      1 |
|  8 | 關羽   |        1 |   22 |      2 |
|  9 | 曹操   |        1 |   20 |   NULL |
|  1 | 劉備   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 張飛   |        2 |   21 |      1 |
|  8 | 關羽   |        1 |   22 |      2 |
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+

-- 隻需要保證字段數量一樣，不需要每次拿到的資料類型都一樣
-- 隻保留第一個select的字段名
select id, name, age from my_student
union all
select name, id, age  from my_student;
+--------+--------+------+
| id     | name   | age  |
+--------+--------+------+
| 1      | 劉備   |   18 |
| 2      | 李四   |   19 |
| 3      | 王五   |   20 |
| 7      | 張飛   |   21 |
| 8      | 關羽   |   22 |
| 9      | 曹操   |   20 |
| 劉備   | 1      |   18 |
| 李四   | 2      |   19 |
| 王五   | 3      |   20 |
| 張飛   | 7      |   21 |
| 關羽   | 8      |   22 |
| 曹操   | 9      |   20 |
+--------+--------+------+
           

3、order by的使用

(select * from my_student where gender = 1 order by age desc)
union
(select * from my_student where gender = 2 order by age asc);
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  2 | 李四   |        1 |   19 |      1 |
|  7 | 張飛   |        2 |   21 |      1 |
|  1 | 劉備   |        1 |   18 |      2 |
|  3 | 王五   |        2 |   20 |      2 |
|  8 | 關羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+

-- order by 要生效，必須使用limit 通常大于表的記錄數
(select * from my_student where gender = 1 order by age desc limit 10)
union
(select * from my_student where gender = 2 order by age asc limit 10);
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  7 | 張飛   |        2 |   21 |      1 |
|  2 | 李四   |        1 |   19 |      1 |
|  1 | 劉備   |        1 |   18 |      2 |
|  3 | 王五   |        2 |   20 |      2 |
|  8 | 關羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+