假设我有一个草稿表:每个Parent都有唯一的ID,并且parent列设置为null。
草案:
id | parent_id | is_deleted
--- ------------ ------------
1 | null | 0
2 | 1 | 0
3 | 1 | 1
4 | null | 0
5 | 4 | 1
6 | 4 | 1
7 | 4 | 0
8 | null | 0
9 | 8 | 1
现在我想获取仅删除了所有父母的列表。
select id
from draft
where id in (
select parent_id
from draft
where id in (1, 2, 3, 5, 7, 9)
group by parent_id
having sum(case when is_deleted 0 then 1 else 0) <= 0
)
where is_deleted = 0;
这里的问题是,它总是返回父级的1、4和8。但是应该只返回8。
更新:我想要父行而不是父id。
最佳答案
这应该工作:
select * from draft
where id in
(
select parent_id from draft a where is_deleted = 1
and not exists
(select 1 from draft b where a.parent_id = b.parent_id and is_deleted = 0)
)
您也可以使用
JOIN
代替IN
select draft.* from draft
join(
select parent_id from draft a where is_deleted = 1
and not exists
(select 1 from draft b where a.parent_id = b.parent_id and is_deleted = 0)
) p on parent.id = p.parent_id
关于mysql - 仅当在mysql查询中删除所有子级时,才检索父行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50533631/