ORDER BY和LIMIT之后的UNION

My goal is to execute two different queries and then combine them.
My code is:

我的目标是执行两个不同的查询，然后将它们组合起来。我的代码是：

SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0,1 
UNION   
SELECT * FROM some tables WHERE ...

I get the following error:

我收到以下错误：

#1221 - Incorrect usage of UNION and ORDER BY

＃1221 - UNION和ORDER BY的使用不正确

It is important that ORDER BY is only for the first query. How can I perform this task?

重要的是ORDER BY仅用于第一个查询。我该如何执行此任务？

4 个解决方案

#1

You can use parenthesis to allow the use of ORDER/LIMIT on individual queries:

您可以使用括号允许在单个查询中使用ORDER / LIMIT：

(SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0, 1)
UNION   
(SELECT * FROM some tables WHERE ...)
ORDER BY 1   /* optional -- applies to the UNIONed result */
LIMIT 0, 100 /* optional -- applies to the UNIONed result */

#2

SELECT * FROM (SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0,1) x
UNION ALL
SELECT * FROM some tables WHERE ...

Note the use of UNION ALL:

注意使用UNION ALL：

UNION removes duplicate rows from the result set and the DB orders all the rows before doing this (so the entire result set is sorted)
UNION从结果集中删除重复的行，并且DB在执行此操作之前对所有行进行排序（因此整个结果集已排序）
UNION ALL preserves both order and duplicates
UNION ALL保留订单和重复项

#3

just put everything in round brackets:

把所有东西放在圆括号中：

(SELECT * FROM table1 ORDER BY datetime  )
UNION   
(SELECT * FROM table2 ORDER BY datetime DESC)

#4

(SELECT user_id AS id FROM tbl_user)
UNION
(SELECT address_id AS id FROM tbl_address)
ORDER BY id ASC LIMIT 10

#1