SQL:选择与UNION, ORDER BY和LIMIT。

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.

我得到的错误是在联合之后的顺序，但是我想要这些查询在合并到一个之前，然后限制到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 ;-)

我希望你能理解，我想做什么，可以帮助我;

5 个解决方案

#1

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中(您没有指定)。订单操作是在数据被选中之后进行的，所有的工会、BYs等都已经执行了。

See SQL Server: ORDER BY in subquery with UNION for a way around this.

参见SQL Server:通过与UNION进行子查询的顺序来解决这个问题。

#2

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
    )

#3

An order by will affect the ENTIRE union.

一个命令会影响整个联盟。

Anyway, it looks like you want the rows nearest to now. You could try this:

不管怎样，看起来你想要离现在最近的行。你可以试试这个:

SELECT   time, x, y, z 
FROM     db 
ORDER BY ABS(time - now) ASC
LIMIT    10

#4

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实现之间可能有所不同。

#5

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函数，如上面所示，否则它是不必要的。

#1