mysql - MySql 自联接查询
问题描述
我有这张桌子
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
子句。
解决方案
您可以在这里使用聚合:
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
andHAVING
子句都是sargable,并且应该能够利用这个索引。
推荐阅读
- arrays - 从数组中检索数据,从 Laravel 中的特定第 n 项开始
- java - JSCH SFTP 文件传输 - 文件中的数据已损坏
- generics - 为什么我收到此代码的“接收器类型不匹配”错误
- javascript - 是否可以在 JavaScript 中自动字符串化?
- ios - swift中的YouTube直播API
- java - 在 java 测试中强制执行 assertj
- javascript - Paylod for ios 静默推送通知使用 JavaScript 中的 Firebase Cloud Function 更新标签栏项目徽章
- javascript - 如何检查我的jsp文件是否转换为UTF-8
- javascript - 使用 Firestore 的 snapshot.forEach 创建表
- javascript - Javascript Loop 不等待 Firebase 完成上传多个文件