我是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

08-04 17:09