mysql> describe skill_usage; ---------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ---------- --------- ------ ----- --------- ------- | skill_id | int(11) | NO | MUL | NULL | || job_id | int(11) | NO | MUL | NULL | | ---------- --------- ------ ----- --------- ------- 6 C: c! F/ C( g& e3 M
并且知道在我的数据中,有一个job_id(6) 用于skill_id3 和 4: 5 T2 l' Y- d% H. Z
" w1 Y& b) \( F0 L! Z6 s
mysql> select * from skill_usage; ---------- -------- | skill_id | job_id | ---------- -------- | | 1 || | 2 || 3 3 3 | 3 | 这是我试过的:[code]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 AND s2.skill_id IN (3)4) 3 @+ g* b( i0 i& b) g) r$ `
我认为这意味着找到一切job_id与skill_id3 和skill_id4匹配的”。4 s- d: y3 s5 ?/ w4 X
显然不是:- p- c# _9 k# |* v: e" b
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 -> AND s2.skill_id IN -------- | job_id | -------- | 3 || 4 || 6 | -------- 3 rows in set (0.00 sec)/ E8 P' k+ t- C* r/ @
我做错了什么?如何阅读我的查询?我想是时候写一本好书或 了Udemy 课程,但我没有自己的封面。 : K$ h9 `4 K& }7 A1 ~5 w( J8 i正确找到我的查询job_id= 6,但是错了(IMO)找到了job_id3 和 4AND条款。 & i9 Y4 d. b) X0 e9 V/ n4 L/ {6 i I1 k 4 R& n4 I8 L5 T1 u! F& y. V3 b解决方案: - ]& p* E6 |% ]8 v' h/ d$ K! ? 您可以在这里使用聚合物:. l; L% S8 |) Y. I6 H6 d* M
SELECT job_idFROM skill_usageWHERE skill_id IN (3,4)GROUP BY job_idHAVING MIN(skill_id) MAX(skill_id); / h; F+ I- i# T( J1 Z! k
本查询应受益于以下索引:0 B; M1 s0 V; v9 Z
CREATE INDEX idx ON skill_usage (skill_id,job_id); 9 X* y; J9 J3 V