MySQL UNION 和 ORDER - 单个表上的多个选择查询

Question

我正在尝试从一张表中选择最多 250 行。首先，我想选择 ALL 列 locked='1' 的行，然后选择 X 列 的行locked = '0' 按列 since(时间戳)排序。

X 是 250 - 第一个查询的行数。

我试过这个:

select * from (
select * from aktivni as a1 where locked = '1'
union
select * from aktivni as a2 where locked = '0'
) as tb1 limit 250

但是有了这个，我无法通过第二个查询获得正确的值(未按 since 排序)。

如果我这样做:

select * from (
select * from aktivni as a1 where locked = '1'
union
select * from aktivni as a2 where locked = '0' ORDER BY since DESC
) as tb1 limit 250

所有内容都按 since 排序，我没有得到 ALL 列 locked 设置为 1 的值。

工会是正确的做法还是我可以做其他事情？

解决方法是在 php 中编写代码来执行此操作。首先我会选择所有:SELECT * FROM table WHERE locked = '1'

比我计算行数并使用第二个查询获取剩余行SELECT * FROM table WHERE locked = '0' LIMIT (250 - num_rows_from_first_query) 但我想避免这种情况。

Answer 1

不需要 union - 您可以在 order by 中使用 limit 条件:

select locked, since
from aktivni cross
order by locked = '1' desc, since desc
limit 250

• SQL Fiddle Demo