MySql自连接查询

Question

我有这张桌子

mysql> describe skill_usage;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| skill_id | int(11) | NO   | MUL | NULL    |       |
| job_id   | int(11) | NO   | MUL | NULL    |       |
+----------+---------+------+-----+---------+-------+

并且要知道，在我的数据中，有一个单独的job_id (6)，用于skill_id 3和4：

mysql>  select * from skill_usage;
+----------+--------+
| skill_id | job_id |
+----------+--------+
|        1 |      1 |
|        2 |      2 |
|        3 |      3 |     <----  matches only one part of the AND clause
|        3 |      4 |     <----  matches only one part of the AND clause
|        2 |      5 |
|        3 |      6 |     <==== matches both parts of the AND clause
|        4 |      6 |     <====
|        2 |      7 |
+----------+--------+
8 rows in set (0.00 sec)

以下是我尝试过的：

SELECT DISTINCT s1.job_id FROM skill_usage AS s1 
  INNER JOIN skill_usage AS s2 ON s1.job_id = s2.job_id
    WHERE s1.skill_id IN (3,4)
    AND   s2.skill_id IN (3,4)

我认为这意味着“查找与skill_id 3和skill_id 4同时匹配的所有skill_id”。

显然不是：

mysql> SELECT DISTINCT s1.job_id FROM skill_usage AS s1
    ->   INNER JOIN skill_usage AS s2 ON s1.job_id = s2.job_id
    ->     WHERE s1.skill_id IN (3,4)
    ->     AND   s2.skill_id IN (3,4);
+--------+
| job_id |
+--------+
|      3 |
|      4 |
|      6 |
+--------+
3 rows in set (0.00 sec)

我做错什么了？我的查询应该怎么读？我认为是时候去读一本好书或者上一门乌德米课程了，但我自己的封面没有一个是自己参加的。

我的查询是正确地找到了job_id = 6，但错误地找到了job_id 3和4，我希望它们会失败AND子句。

Answer 1

替代解决方案。

(参见db小提琴)

select s1.job_id
  from skill_usage s1
  where s1.skill_id = 3
    and s1.job_id in (
                       select s2.job_id
                         from skill_usage s2
                        where s2.skill_id = 4
                     )

Answer 2

您可以在这里使用聚合：

SELECT job_id
FROM skill_usage
WHERE skill_id IN (3, 4)
GROUP BY job_id
HAVING MIN(skill_id) <> MAX(skill_id);

此查询应受益于以下索引：

CREATE INDEX idx ON skill_usage (skill_id, job_id);

编写的WHERE和HAVING子句都是sargable的，并且应该能够利用这个索引。