在 MySQL 查询中结合 UNION 和 LIMIT 操作

我有一个工作和一个公司表，我想提取20个满足以下标准的作业:

只有两家公司提供工作
每家公司最多只能提供10个工作岗位

我已经用 UNION DISTINCT尝试了以下 SELECT，但问题是 LIMIT 0,10适用于整个结果集。我希望它适用于每个公司。

如果每个公司没有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

我是 MySQL 的新手，所以意识到可能有一个更聪明的方法来代替 UNION，所以任何改进的建议都是非常受欢迎的。

102297 次浏览

小开

最佳答案

Quoting the docs,

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the 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);

小开

Improving on Alex's answer and based on Joe's observation, the following should work in SQLite:

SELECT * FROM
(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);

小开

In Teradata we can't use union with top queries as it, if you do you get an error, which needs to be tweaked as shown below. Adding solution for Teradata users.