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

2021-11-20 00:00:00 sql limit union mysql

我有一个 Jobs 和一个 Companies 表，我想提取 20 个满足以下条件的工作:

I have a Jobs and a Companies table, and I want to extract 20 jobs that meet the following criteria:

  1. 仅来自两 (2) 家指定公司的职位
  2. 每家公司最多可以有 10 个工作机会

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

I have tried the following SELECT with UNION DISTINCT, but the problem is that the LIMIT 0,10 applies to the whole result set. I want it to apply to each of the companies.

如果每个公司没有 10 个职位，那么查询应该返回它找到的所有职位.

If there aren't 10 jobs per company, then the query should return all the jobs it finds.

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，所以绝对欢迎任何改进建议.

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.

推荐答案

引用 文档,

将 ORDER BY 或 LIMIT 应用于单独的 SELECT，放置子句在括起来的括号内选择:

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);

相关文章