mysql - 在 SQL 中有两个不同的顺序和限制语句

Question

我有一个 select 语句，我需要首先按相关性对其进行排序，获得前十名相关性，按 rand() 对其进行排序，然后将其限制为 1，但我认为这样做是不可能的有 2 个不同的顺序和限制语句。所以，我的问题是，是否有解决此问题的方法？谢谢。

SELECT id, input, response, 
MATCH (
input
)
AGAINST (
 'cat frogs trees'
IN NATURAL LANGUAGE
MODE
) AS relevance
FROM allData
HAVING relevance >0
ORDER BY relevance DESC
LIMIT 10
ORDER BY RAND()
LIMIT 1

Answer 1

使用子查询:

SELECT t.*
FROM (SELECT id, input, response, 
             MATCH (input) AGAINST ('cat frogs trees' IN NATURAL LANGUAGE MODE) AS relevance
      FROM allData
      HAVING relevance > 0
      ORDER BY relevance DESC
      LIMIT 10
     ) t
ORDER BY RAND()
LIMIT 1;