在MySQL查询中结合UNION和LIMIT操作 [英] Combining UNION and LIMIT operations in MySQL query

问题描述

我有一个职位和 Company 表，我想提取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个工作机会

我用UNION DISTINCT尝试了以下SELECT，但是问题是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，放置子句 括在括号内 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);

这篇关于在MySQL查询中结合UNION和LIMIT操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！