MySQL 加入同一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10576033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Join Same Table
提问by execv
I have the table 'meta_data' with the following fields:
我有包含以下字段的表“meta_data”：
- id
- post_id
- meta_key
- meta_value
- ID
- post_id
- 元键
- 元值
I'd like to loop through and display a list of EACH post (post_id) that has an entry for meta_key='abc'but notone for meta_key='def'
我想循环并显示每个帖子 ( post_id)的列表，这些帖子有一个条目，meta_key='abc'但没有一个条目meta_key='def'
Basically, every post that has a meta_key='abc'entry shouldhave a meta_key='def'entry. I want to generate the list so I can add the missing meta_key='def'entries.
基本上，每个有meta_key='abc'条目的帖子都应该有一个meta_key='def'条目。我想生成列表，以便我可以添加缺少的meta_key='def'条目。
回答by Gratzy
To achive this you should use the LEFT OUTER JOINoperation joining the same table.
要实现这一点，您应该使用LEFT OUTER JOIN操作加入同一个表。
SELECT a.*
FROM meta_data a
LEFT OUTER JOIN meta_data b ON a.post_id = b.post_id AND b.meta_value = 'def'
WHERE
a.meta_value = 'abc'
AND b.id IS null
回答by Bohemian
Make an outer (left) join to itself, filtering on those records that don'tmatch by looking for rows with a null id in the joined table:
对自身进行外部（左）连接，通过在连接表中查找具有空 ID 的行来过滤那些不匹配的记录：
select t1.*
from meta_data t1
left join meta_data t2 on t2.post_id = t1.post_id and t2.meta_key='def'
where t1.meta_key='abc'
and t2.id is null
