mysql - Mysql - 获取同一表中的行交集

Question

我有这个简单的表（称为jobs_criterias）：

+-----------+---------+-------------+
| id        | job_id  | criteria_id |
+-----------+---------+-------------+
|       101 | 4       | 3           |
|       105 | 6       | 5           |
|       130 | 8       | 5           |
|       132 | 9       | 5           |
|       133 | 6       | 7           |
|       150 | 7       | 8           |
|       160 | 8       | 9           |
+-----------+---------+-------------+

我想找到job_id相同的criteria_id。

例如，我想job_id搜索criteria_id IN (5, 7). 它应该返回 6 和样本数据（只有job_id= 6 匹配两者criteria_ids）

经过一些搜索，我使用了这个查询：

SELECT DISTINCT 
   job_id
FROM jobs_criterias jc1
   INNER JOIN jobs_criterias jc2 USING(job_id)
WHERE jc1.criteria_id = 5 AND jc2.criteria_id = 7

它可以工作，但这个查询不容易扩展。我使用 PHP 框架中的查询构建器，很难构建这种查询。

有没有更简单的方法来实现这一点？

标签： mysqlsql

Answer 1

您可以使用分组查询来计算criteria_id给定job_id匹配的数量，仅选择与所有匹配的那些：

SELECT job_id
FROM jobs_criterias
WHERE criteria_id IN (5, 7)
GROUP BY job_id
HAVING COUNT(DISTINCT criteria_id) = 2

输出

6

dbfiddle 上的演示