MySQL中使用UNION和ORDER BY的注意事项
在MySQL中,
UNION
和
ORDER BY
是两个常用的关键字,分别用于合并多个表的结果和排序结果。但是,当这两个关键字一起使用时,有一些需要注意的问题。
背景
有两个表table1、table2:
CREATE TABLE table1 (id INT,name VARCHAR(50)
);CREATE TABLE table2 (id INT,name VARCHAR(50)
);INSERT INTO table1 (id, name) VALUES (1, 'Alice');
INSERT INTO table1 (id, name) VALUES (2, 'Tom');
INSERT INTO table1 (id, name) VALUES (3, 'Bob');INSERT INTO table2 (id, name) VALUES (4, 'David');
INSERT INTO table2 (id, name) VALUES (5, 'Charlie');
INSERT INTO table2 (id, name) VALUES (6, 'Evelina');
现在需要合并这两个表,按照name
字段排序
需求一:先union再order by
实际开发中,一般都是这种需求。
写法(正确)
SELECT name FROM table1
UNION
SELECT name FROM table2
ORDER BY name DESC;
我们只需要在最后一个SELECT
语句中包含ORDER BY
子句,这样就可以得到正确的排序结果。
name |
---|
Tom |
Evelina |
David |
Charlie |
Bob |
Alice |
需求二:先order by再union
实际开发中,偶尔会是这种需求。
写法一(错误)
SELECT name FROM table1 ORDER BY name DESC
UNION
SELECT name FROM table2 ORDER BY name DESC
;
这样会报错Incorrect usage of UNION and ORDER BY
,因为union
在没有括号的情况下只能使用一个order by
。
写法二(错误)
(SELECT name FROM table1 ORDER BY name DESC)
UNION
(SELECT name FROM table2 ORDER BY name DESC)
;
这样虽然不会报错,但是两个order by
并没有生效,因为子查询中有ORDER BY
而没有LIMIT
,那么优化器会把ORDER BY
优化掉。
name |
---|
Alice |
Tom |
Bob |
David |
Charlie |
Evelina |
写法三(错误)
SELECT * FROM (SELECT name FROM table1 ORDER BY name DESC) t1
UNION
SELECT * FROM (SELECT name FROM table2 ORDER BY name DESC) t2
查询结果与写法二是一样的。
写法四(正确)
SELECT * FROM (SELECT name FROM table1 ORDER BY name DESC LIMIT 99999999) t1
UNION
SELECT * FROM (SELECT name FROM table2 ORDER BY name DESC LIMIT 99999999) t2
这样得到正确的排序结果
name |
---|
Tom |
Bob |
Alice |
Evelina |
David |
Charlie |
写法五(正确)
(SELECT name, 1 as table_order FROM table1 ORDER BY name DESC)
UNION
(SELECT name, 2 as table_order FROM table2 ORDER BY name DESC)
ORDER BY table_order, name DESC
name | table_order |
---|---|
Tom | 1 |
Bob | 1 |
Alice | 1 |
Evelina | 2 |
David | 2 |
Charlie | 2 |
总结
- 先
UNION
后ORDER BY
时,只需要在最后一个SELECT
语句中包含ORDER BY
; - 先
ORDER BY
后UNION
时,由于优先级问题,需要将子查询用括号括起来,且ORDER BY后面必须有
LIMIT`;