我有这张桌子
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)
我认为这意味着“找到job_id
同时匹配skill_id
3和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课程的时间,但我所拥有的并没有掩盖自我。
我的查询正确找到job_id
= 6,但错误地找到了(IMO),找到job_id
3和4。我希望它们使AND
子句失败。
替代解决方案。
(请参阅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
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句