我有一个 Jobs 和一个 Companies 表,我想提取 20 个满足以下条件的工作:
我用 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 更智能的方法来执行此操作,因此欢迎提出任何改进建议。
最佳答案
引用 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);
https://stackoverflow.com/questions/1415328/