我是sql的初学者,希望您能对我有所帮助:
MySQL服务器5
表格1
+ ---- + ---------- + ------------ + ------ +
| ID | USERCODE | ENTITYCODE | DATE |
+ ---- + ---------- + ------------ + ------ +
| x | CODE123 | 010011 | ... |
| x | CODE123 | 010012 | ... |
| x | CODE123 | 010045 | ... |
+ ---- + ---------- + ------------ + ------ +
表2
+ ---- + ---------- + ------------ + ------------- + ------ ------- +
| ID | USERCODE | ENTITYCODE | OTHERFIELD1 | OTHERFIELD2 |
+ ---- + ---------- + ------------ + ------------- + ------ ------- +
| x | CODE123 | 010011 | ... | ... |
| x | CODE123 | 010012 | ... | ... |
| x | CODE123 | 010045 | ... | ... |
| .. | .. | .. | ... | ... |
+ ---- + ---------- + ------------ + ------------- + ------ ------- +
实体表
+ ------------ + ------------- + ------------- + -------- ----- +
| ENTITYCODE | OTHERFIELD1 | OTHERFIELD2 |特殊代码|
+ ------------ + ------------- + ------------- + -------- ----- +
| 010011 | .. | .. | 100 |
| 010012 | .. | .. | 105 |
| 010045 | .. | .. | 111 |
| .. | .. | ... | ... |
+ ------------ + ------------- + ------------- + -------- ----- +
预期结果
约束:表1,表2和实体中存在的所有USERCODE,ENTITYCODE。specialcode= 105
+ ----------- + ------------ +
| USERCODE | ENTITYCODE |
+ ----------- + ------------ +
| CODE123 | 010012 |
+ ----------- + ------------ +
最佳答案
SELECT a.USERCODE , a.ENTITYCODE
FROM table1 a
JOIN table2 b ON a.USERCODE = b.USERCODE AND a.ENTITYCODE = b.ENTITYCODE
JOIN entity c ON a.ENTITYCODE = c.ENTITYCODE
WHERE c.SPECIALCODE = 105