sql - MySQL - 将 UNION 与 LIMIT 结合使用

Question

我注意到了

(SELECT title, relavency, 'search1' as source FROM search1ORDER BY relavency DESCLIMIT 10)UNION (SELECT title, relavency, 'search2' as source FROM search2ORDER BY relavency DESCLIMIT 10)ORDER BY relavency DESC LIMIT 10

最后的 LIMIT 10 不起作用。为什么？

错误是

“错误代码 1064:...在 'LIMIT 1000' 附近使用”

为什么 mysql workbench 将 LIMIT 10 检测为 LIMIT 1000，但如果它是 1000，它仍然可以工作？

Answer 1

您的查询可以使用带别名的嵌套子查询来重写。这应该适合你:

SELECT u.* FROM (
    (SELECT s1.title, s1.relavency, 'search1' as source FROM search1 AS s1
    ORDER BY s1.relavency DESC
    LIMIT 10)
        UNION 
    (SELECT s2.title, s2.relavency, 'search2' as source FROM search2 AS s2
    ORDER BY s2.relavency DESC
    LIMIT 10)
) AS u ORDER BY u.relavency DESC 
LIMIT 10

仅供引用:您拼错了“relevancy”，但我保留了拼写错误，以便查询正常运行。