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

发布于 2024-08-05 00:40:52 字数 737 浏览 5 评论 0原文

我有一个 Jobs 和一个 Companies 表，我想提取 20 个满足以下条件的职位：

仅来自两 (2) 个指定公司的职位
最多可以是每家公司 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 来完成此操作，因此绝对欢迎任何改进建议。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遥远的绿洲 2024-08-12 00:40:52

引用文档，

将 ORDER BY 或 LIMIT 应用于
单独的 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);

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

回复收藏 0 原文

嗼ふ静 2024-08-12 00:40:52

改进 Alex 的答案并根据 Joe 的观察，以下内容应该在 SQLite 中工作：

SELECT * FROM 
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
SELECT * FROM 
(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 * FROM 
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

回复收藏 0 原文