【Mysql教程】数据库里如何联合查询 union方法详解

所需工具：

Mysql

聪明的大脑文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

勤劳的双手文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

 文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

注意：本站只提供教程，不提供任何成品+工具+软件链接，仅限用于学习和研究，禁止商业用途，未经允许禁止转载/分享等文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

 文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

教程如下

前言：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

将多个查询结果的结果集合并到一起（纵向合并），字段数不变，多个查询结果的记录数合并文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

1、应用场景

同一张表中不同结果合并到一起展示：男生升高升序，女生升高降序
数据量较大的表，进行分表操作，将每张表的数据合并起来显示文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

2、基本语法

[php]文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

select 语句
union [union 选项]
select 语句;文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/12272.html

[/php]

union 选项 和select 选项基本一致

distinct 去重，默认
all 保存所有结果

[php]

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

[/php]

3、order by的使用

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

[php]

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

[/php]