使用RAND()的命令在MySQL联合查询中使用单独的限制语句。

I want to run a query like this, so that I will always get 20 results, even if the 1st SELECT statement does not have enough data to return 10 results.

我希望运行这样的查询，这样我将总是得到20个结果，即使第一个SELECT语句没有足够的数据返回10个结果。

(
SELECT t.column1, t.column2
FROM table t 
WHERE t.status = 1 
LIMIT 10
)
UNION
(
SELECT t.column1, t.column2 
FROM table t
WHERE t.status = 2 
LIMIT 20 
)
LIMIT 20 

But I then want to ORDER BY RAND() and make sure that all the available results from the first SELECT statement are included in the final result set.

但是，我想要通过RAND()来排序，并确保在最终结果集中包含了第一个SELECT语句的所有可用结果。

When I add ORDER BY RAND() to the end of this query, it gives me result sets that do not include all 10 results from the first SELECT, I assume because it is really getting 30 rows and then choosing 20 at random from the 30.

当我在这个查询的末尾添加ORDER BY RAND()时，它给我的结果集不包括第一个SELECT的所有10个结果，我假设因为它实际上得到30行，然后从30个随机选择20个。

Any ideas would be appreciated.

任何想法都会被欣赏的。

Here is what worked for me:

这就是我的工作:

SELECT x.*
FROM ((
SELECT t.column1, t.column2
FROM table t 
WHERE t.status = 1 
LIMIT 10
)
UNION
(
SELECT t.column1, t.column2 
FROM table t
WHERE t.status = 2 
LIMIT 20 
)
LIMIT 20) x
ORDER BY RAND

1 个解决方案

#1

Should work if you order your UNION results by what SELECT they are “from” first, before the LIMIT 20 is applied:

如果你的工会的结果是由“从”开始的，在限定的20之前，你应该工作:

SELECT … FROM
(
  (
    SELECT t.column1, t.column2, 0 AS ordervalue FROM … WHERE …
    LIMIT 10
  )
  UNION
  (
    SELECT t.column1, t.column2, 1 FROM … WHERE …
    LIMIT 20 
  )
  ORDER BY ordervalue ASC # this will make the values from your first SELECT come
                          # first, then the ones from the second

  LIMIT 20 # of those ordered values, the first 20 are taken
)
ORDER BY RAND()

I introduced a static value as ordervalue here – in your special case it would not be necessary, since you select by status 1 or 2, so you could order by that value directly. But if that was not the case (or the status values would be different), that “pseudo” column value would be the way to go.

我在这里引入了一个静态值作为ordervalue——在您的特殊情况下，它是不需要的，因为您选择了状态1或2，所以您可以直接按这个值排序。但是，如果不是这样(或者状态值会不同)，那么“伪”列值就是方法。