在 MySQL 查询中结合 UNION 和 LIMIT 操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1415328/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combining UNION and LIMIT operations in MySQL query
提问by Alex Martelli
I have a Jobsand a Companiestable, and I want to extract 20 jobs that meet the following criteria:
我有一个Jobs和一个Companies表，我想提取满足以下条件的 20 个工作：
- Jobs only from two (2) named companies
- There can at most be 10 jobs per company
- 仅来自两 (2) 家指定公司的工作
- 每个公司最多可以有 10 个工作岗位
I have tried the following SELECTwith UNION DISTINCT, but the problem is that the LIMIT 0,10applies to the whole result set. I want it to apply to each of the companies.
我曾尝试以下SELECT同UNION DISTINCT，但问题是，LIMIT 0,10适用于整个结果集。我希望它适用于每家公司。
If there aren't 10 jobs per company, then the query should return all the jobs it finds.
如果每个公司没有 10 个工作，那么查询应该返回它找到的所有工作。
SELECT c.name, j.title, j.`desc`, j.link
FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company1')
UNION DISTINCT
SELECT c.name, j.title, j.`desc`, j.link
FROM jobs_job j
INNER JOIN companies_company c ON j.company_id = c.id
WHERE c.name IN ('Company2')
ORDER by name, title
LIMIT 0,10
I am new to MySQL, so realise there may be a smarter way to do this instead of with UNION, so any suggestions for improvements are definitely welcome.
我是 MySQL 的新手，所以意识到可能有更聪明的方法来代替 UNION，所以绝对欢迎任何改进建议。
回答by Alex Martelli
Quoting the docs,
引用文档，
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:
要将 ORDER BY 或 LIMIT 应用于单个 SELECT，请将子句放在包含 SELECT 的括号内：
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
