MySql 自 JOIN 查询 [英] MySql self JOIN query
问题描述
我有这张桌子
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:
and know that in my data, there is a single job_id
(6) which was used for both skill_id
3 and 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 匹配的job_id
".
which I thought meant "find all job_id
which matches both skill_id
3 and skill_id
4".
显然不是:
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)
我做错了什么?我的查询应该如何阅读?我认为是时候写一本好书或 Udemy 课程了,但我没有自己的封面自我加入.
What am I doing wrongly? How should my query read? I think that it's time for a good book or Udemy course, but none that I own cover self join.
我的查询正确地找到了 job_id
= 6,但错误地(IMO)找到了 job_id
3 和 4.我希望它们失败 AND
子句.
My query is correctly finding job_id
= 6, but wrongly (IMO), finding job_id
3 and 4. I would expect them to fail the AND
clause.
推荐答案
替代解决方案.
(参考 db fiddle)
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
)
这篇关于MySql 自 JOIN 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!