mysql - 删除用户评论但保留它 - 计数

Question

当用户删除帖子或评论时，我刚刚在 delete 列上设置为 1。

问题是，我使用计数来显示用户对该帖子有多少评论。

select c.nome, p.foto, c.user, p.user, count(DISTINCT comentarios.id) as comentarios_count from posts p 
join cadastro c on p.user=c.id 
left join comentarios on comentarios.foto = p.id
where p.delete='0' and comentarios.delete='0'
group by p.id
order by p.id desc limit ?

问题是commentarios.delete='0'将仅选择至少有一条评论的帖子。我想选择所有帖子，但只计算 delete 设置为 0 的评论。怎么了？

Answer 1

选择全部并减去删除内容怎么样

select c.nome, p.foto, c.user, p.user, count(DISTINCT comentarios.id) -sum(comentarios.delete) as comentarios_count from posts p 
join cadastro c on p.user=c.id 
left join comentarios on comentarios.foto = p.id
where p.delete='0' 
group by p.id
order by p.id desc limit ?