SQL：SELECT 与 UNION、ORDER BY 和 LIMIT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4696947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: SELECT with UNION, ORDER BY and LIMIT
提问by Tobias
I'm getting Errors that ORDER by should come after UNION but i want these to queries ordered before combined to one and then limited to 10.
我收到 ORDER by 应该在 UNION 之后出现的错误，但我希望这些查询在合并为 1 之前排序，然后限制为 10。
SELECT *
FROM (SELECT time, x, y, z
FROM db
WHERE time >= now
ORDER by time, x
UNION
SELECT time, x, y, z
FROM db
WHERE time < now
ORDER by time, x)
LIMIT 10
I hope you understand, what I'm trying to do and can help me ;-)
我希望你理解我正在尝试做的事情并且可以帮助我;-)
采纳答案by coreyward
That's not how it works, at least in MySQL (you didn't specify). The ORDER operation comes after the data is selected and all UNIONs, GROUP BYs, etc. have been performed.
这不是它的工作原理，至少在 MySQL 中（您没有指定）。ORDER 操作是在选择数据并且所有 UNION、GROUP BY 等都已执行之后进行的。
See SQL Server: ORDER BY in subquery with UNIONfor a way around this.
请参阅SQL Server: ORDER BY in subquery with UNION以了解解决此问题的方法。
回答by hmaxf2
if you have a very complex query in SQLite but need to use UNION with ordering, then you can try
如果您在 SQLite 中有一个非常复杂的查询，但需要使用 UNION 进行排序，那么您可以尝试
select * from (
select * from b ORDER BY date asc
)
UNION
select * from (
select * from b ORDER BY name desc
)
UNION
select * from (
select * from b ORDER BY gender asc
)
回答by Andomar
An order by will affect the ENTIRE union.
Order by 将影响整个联合。
Anyway, it looks like you want the rows nearest to now. You could try this:
无论如何，看起来您想要最接近now. 你可以试试这个：
SELECT time, x, y, z
FROM db
ORDER BY ABS(time - now) ASC
LIMIT 10
回答by Nicholas Carey
In "standard" (for some definition of "standard") SQL;
在“标准”中（对于“标准”的某些定义）SQL；
select top 10 *
from ( select time,x,y,z from db where time > now
union select time,x,y,z from db where time < now
) t
order by t.time
How to limit the number of rows in the result set may vary between SQL implementations.
如何限制结果集中的行数可能因 SQL 实现而异。
回答by Allan
Any easy solution I applied was as follows :
我应用的任何简单解决方案如下：
SELECT
name,
TO_DATE(date1, 'DD-MON-YYYY HH:MI AM')
FROM TableX
UNION
SELECT
name,
TO_DATE(date2, 'DD-MON-YYYY HH:MI AM')
FROM TableY
ORDER BY 2 DESC;
This orders the results by the second (date) column.
这按第二个（日期）列对结果进行排序。
If the date column is a string, you can apply the TO_DATE function as shown above, else it is not necessary.
如果日期列是字符串，则可以应用如上所示的 TO_DATE 函数，否则不需要。
