使用UNION关键字进行多表关联时,往往需要涉及到排序问题。
在下面的SQL中,板块A、板块B中均添加了 ORDER BY 排序,导致报错。
SELECT
u.`name`,
u.age
FROM `user` u
ORDER BY u.`name`, u.age
UNION
SELECT
u2.`name`,
u2.age
FROM user2 u2
ORDER BY u2.`name`, u2.age
对SQL进行如下改写后,排序成功。
(
SELECT
u.`name`,
u.age
FROM `user` u
)
UNION
(
SELECT
u2.`name`,
u2.age
FROM user2 u2
)
ORDER BY name, age
解决方案:
THe simple manual example
(SELECT a FROM tbl_name INNER JOIN xx WHERE conditons)
UNION
(SELECT a FROM tbl_name INNER JOIN xx WHERE conditions)
ORDER BY a
参考文章: [Err] 1250 - Table ‘xxx‘ from one of the SELECTs cannot be used in field list