mysql union limit,在MySQL查询中结合UNION和LIMIT操作

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

Jobs only from two (2) named companies

There can at most be 10 jobs per company

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.

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

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.

解决方案

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