MySQL 中的 UNION 语句
一、数据准备
-- 创建表
CREATE TABLE test_user (
ID int(11) NOT NULL AUTO_INCREMENT,
USER_ID int(11) DEFAULT NULL COMMENT '用户账号',
USER_NAME varchar(255) DEFAULT NULL COMMENT '用户名',
AGE int(5) DEFAULT NULL COMMENT '年龄',
COMMENT varchar(255) DEFAULT NULL COMMENT '简介',
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- 数据插入语句
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('1', '111', '开心菜鸟', '18', '今天很开心');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('2', '222', '悲伤菜鸟', '21', '今天很悲伤');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('3', '333', '认真菜鸟', '30', '今天很认真');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('4', '444', '高兴菜鸟', '18', '今天很高兴');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('5', '555', '严肃菜鸟', '21', '今天很严肃');
SELECT * FROM test_user u;
一、UNION 和 UNION ALL
UNION
连接数据集关键字,可以将两个查询结果集拼接为一个,会过滤掉相同的记录
UNION ALL
连接数据集关键字,可以将两个查询结果集拼接为一个,不会过滤掉相同的记录
-- 使用UNION
SELECT * FROM test_user u
UNION
SELECT * FROM test_user u;
使用 UNION ,可以看到查询结果只有 5 条数据。
-- 使用UNION ALL
SELECT * FROM test_user u
UNION ALL
SELECT * FROM test_user u;
使用 UNION ALL,可以看到查询结果有 10 条数据。
二、UNION 的执行顺序(UNION 和其他语句一同出现)
from—>on—>join—>where—>group by—>having+(聚合函数)—>select—>distinct—>UNION—>order by—>limit
UNION 的执行顺序在 ORDER BY 之前
请记住这个执行顺序,便可以知道 UNION 和其他语句一同出现的结果。
- UNION 和 WHERE 语句
-- 1、第二个子句中的 where 语句不能同时作用于两个select语句
-- 5 + 1,共计 6 条
SELECT *, 'table1' FROM test_user u
UNION ALL
SELECT *, 'table2' FROM test_user u WHERE AGE = 30;
UNION 在 where 之后,所以第二个表的WHERE先筛选后进行数据集拼接;
如果想要把 where 作用所有结果集,可以通过再嵌套一个 select 。
-- 1、第二个子句中的 where 语句不能同时作用于两个select语句
-- 1 + 1,共计 2 条
-- 写法 1
SELECT * FROM
(
SELECT *, 'table1' FROM test_user u
UNION ALL
SELECT *, 'table2' FROM test_user u
) a
WHERE AGE = 30;
-- 或者使用写法 2
SELECT *, 'table1' FROM test_user u WHERE AGE = 30
UNION ALL
SELECT *, 'table2' FROM test_user u WHERE AGE = 30
;
- UNION 和 GROUP 语句
-- 2、第二个子句中的 group by 语句不能同时作用于两个select语句
-- 5 + 3,共计 8 条
SELECT *, 'table1' FROM test_user u
UNION ALL
SELECT *, 'table2' FROM test_user u GROUP BY AGE;
UNION 在 GROUP BY 之后,所以 table2 的 GROUP BY 先分组后进行数据集拼接;
2. UNION 和 HAVING 语句
-- 3、第二个子句中的 HAVING 语句不能同时作用于两个 select 语句
-- 5 + 1,共计 6 条
SELECT *, 'table1' FROM test_user u
UNION ALL
SELECT *, 'table2' FROM test_user u HAVING AGE = 30 ;
UNION 在 HAVING 之后,所以 table2 的 HAVING 先过滤后进行数据集拼接;
3. UNION 和 ORDER BY 语句
-- 4、第二个子句中的 order by 语句可以同时作用于两个select语句
-- 查询结果整体按照 age 进行了排序
SELECT *, 'table1' FROM test_user u
UNION ALL
SELECT *, 'table2' FROM test_user u ORDER BY AGE;
因为当 UNION(ALL)语句和 ORDER BY语句同时出现,UNION(ALL)语句先执行。
4. UNION 和 LIMIT 语句
-- 只有1条数据,因为LIMIT在UNION之后执行
SELECT *, 'table1' FROM test_user u
UNION ALL
SELECT *, 'table2' FROM test_user u limit 0,1;
只有1条数据,因为 LIMIT 在 UNION 之后执行。
- UNION 、 ORDER BY 和 LIMIT 语句
-- 5、第二个子句中的 order by ,LIMIT 语句同时作用于两个 select 语句 *********
-- 只有1条数据,age=30 UNION--->ORDER BY--->LIMIT
SELECT *, 'table1' FROM test_user u
UNION ALL
SELECT *, 'table2' FROM test_user u order by age desc limit 0,1;
先拼接数据集,在按照 age 排序,最后使用 LIMIT 。
三、MySQL 使用 UNION(ALL) + ORDER 导致排序失效
通过以下两种方式解决:
- 添加 LIMIT 字段
- 额外增加排序字段
- SQL 1 如下
SELECT * FROM test_user u ORDER BY AGE;
2. SQL 2 如下
SELECT * FROM test_user u ORDER BY AGE DESC;
3. 查询结果集
(SELECT *, 'table1' FROM test_user u ORDER BY AGE)
UNION ALL
(SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC);
可以看到此时 ORDER BY 语句失效了。
原因:UNION(ALL) + 会使 ORDER 失效
- 解决办法 1 添加 LIMIT
-- 都加上 LIMIT
( SELECT *, 'table1' FROM test_user u ORDER BY AGE limit 10)
UNION ALL
( SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC limit 10)
最好的解决方案就是先查询后排序,避免上述情况发生。
- 解决办法 2 添加额外的排序字段
select * from
(
( SELECT *, 'table1' AS name, row_number() over(ORDER BY AGE ) AS rn FROM test_user u )
UNION ALL
( SELECT *, 'table2' AS name, row_number() over(ORDER BY AGE DESC) AS rn FROM test_user u )
) a
order by name, rn;
额外需要两个字段,通过 row_number() 进行表内排序,通过 name 字段进行表排序。
相关文章
- Windows 10/11 福利:微软为所有安卓手机带来 Windows 剪贴板同步功能
- 就很凸然,电脑里多出了一个文件···
- HarmonyOS 分布式亲子教育
- 十大面向Windows的免费数据恢复工具
- Windows 7还能升级Windows 11吗?微软披露Windows11升级细节
- 从模型复杂度角度来理解过拟合现象
- 拉格朗日乘数法,一种计算条件极值的方式
- elasticsearch高亮之highlight原理
- elasticsearch查询之大数据集分页查询
- elasticsearch之多索引查询
- 如何使用注解优雅的记录操作日志 | 萌新写开源 01
- elasticsearch支持大table格式数据的搜索
- 解析fiddler返回的部分数据。
- SpringBoot快速配置多数据源(整合MyBatis)
- WebSocket集群分布式改造:实现多人在线聊天室
- MongoDB之TextSearch简介
- 快速搭建Zookeeper和Kafka环境
- 【秒杀系统】秒杀系统实战(四)| 缓存与数据库双写一致性实战
- 【秒杀系统】秒杀系统实战(五)| 如何优雅的完成订单异步处理
- 缓存与数据库双写一致性 深度分析