临沂网站设计价格,爱站网站排行榜,网站营销体系的建设及运营情况,排版设计说明文章目录一、数据准备一、UNION 和 UNION ALL二、UNION 的执行顺序#xff08;UNION 和其他语句一同出现#xff09;三、MySQL 使用 UNION#xff08;ALL#xff09; ORDER 导致排序失效四、UNION 报错语法一、数据准备
-- 创建表
CREATE TABLE test_user (ID int(11) NO…
文章目录一、数据准备一、UNION 和 UNION ALL二、UNION 的执行顺序UNION 和其他语句一同出现三、MySQL 使用 UNIONALL ORDER 导致排序失效四、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)
) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8;-- 数据插入语句
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 ALLSELECT *, 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;因为当 UNIONALL语句和 ORDER BY语句同时出现UNIONALL语句先执行。 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条数据age30 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 使用 UNIONALL 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 语句失效了。 原因UNIONALL 会使 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() over(order by column)进行表内排序再通过 name 字段进行表排序。 四、UNION 报错语法
1. ORDER BY 语法报错
-- 语法错误
SELECT *, table1 FROM test_user u ORDER BY AGE
UNION ALL
SELECT *, table2 FROM test_user u ORDER BY AGE DESC;第一个 SELECT 语句也使用了 ORDER BY 导致报错。 解决方案第一个 SELECT 语句加上括号。
-- 语法正确
(SELECT *, table1 FROM test_user u ORDER BY AGE)
UNION ALL
SELECT *, table2 FROM test_user u ORDER BY AGE DESC;加上括号后虽然不再报错但是第一个 SELECT 语句的排序失效。 那要是上下两个都加上括号呢
-- 语法正确
(SELECT *, table1 FROM test_user u ORDER BY AGE)
UNION ALL
(SELECT *, table2 FROM test_user u ORDER BY AGE DESC);语法不报错但是两个排序都失效了。
原因大家也清楚前面第三小节已经讲过了UNION 在 ORDER BY 语句之前。 2. LIMIT 语法报错
同样对于 LIMIT 语句也是一样的。
-- 语法错误
SELECT *, table1 FROM test_user u limit 0,1
UNION ALL
SELECT *, table2 FROM test_user u limit 0,1;解决方案同样也是第一个 SELECT 语句加上括号。
-- 语法正确1 条记录
(SELECT *, table1 FROM test_user u limit 0,1)
UNION ALL
SELECT *, table2 FROM test_user u limit 0,1;此时语法正确但只返回一行记录。 如果想要返回两条记录就给第二个 SELECT 语句也加上括号。
-- 语法正确2 条记录
(SELECT *, table1 FROM test_user u limit 0,1)
UNION ALL
(SELECT *, table2 FROM test_user u limit 0,1);总结 UNION 后面执行的 ORDER BYLIMIT 语句注意使用时要加括号否则报错。
