联合查询

将多个查询结果的结果集合并到一起（纵向合并），字段数不变，多个查询结果的记录数合并

1、应用场景

1. 同一张表中不同结果合并到一起展示：男生升高升序，女生升高降序
2. 数据量较大的表，进行分表操作，将每张表的数据合并起来显示

2、基本语法

select 语句
union [union 选项]
select 语句;

  

 

  
1
  
2
  
3
 

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 |
+--------+--------+------+

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
  
50
  
51
  
52
  
53
  
54
  
55
  
56
  
57
  
58
  
59
  
60
  
61
  
62
  
63
  
64
  
65
  
66
  
67
  
68
  
69
 

3、order by的使用

联合查询中，使用order by, select语句必须使用括号

(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 |
+----+--------+----------+------+--------+

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
 

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/125064508