mysql - 如何获取mysql中未映射的记录

Question

我使用MySQL数据库，我的表结构如下:

+---------------+----------------+-------------------+
| leave_type_id | leave_type     | leave_type_org_id |
+---------------+----------------+-------------------+
|            23 | Casual Leave   |                33 | 
|            24 | Earn Leave     |                 0 | 
|            33 | Sick Leave     |                 0 | 
|            34 | CL   Leave     |                 0 | 
|            35 | PL   LEAVE     |                23 | 
|            42 | EL   LEAVE     |                 0 | 
+---------------+----------------+-------------------+

我只想从我的表leave_type中获取未映射的记录，这意味着leave_type_org_id中不存在哪个leave_type_id，例如(24,34,35,42)，请尽快向我发送查询作为回复。谢谢。

Answer 1

您可以像这样在同一张表上加入

select t1.* 
from table t1
left join table t2
on t1.leave_type= t2.leave_type_org_id
where t2.leave_type_org_id is null

或者

带有子查询

select * from table 
where leave_type_id not in 
(
    select leave_type_org_id from table  
)