当用户删除帖子或评论时,我在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 ?
问题是comentarios.delete ='0'将仅选择具有至少一条评论的帖子。我想选择所有帖子,但只计算
delete
设置为0的评论。怎么了?
最佳答案
如何选择全部并减去删除
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 ?
关于mysql - 删除用户评论但保留它-计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37061203/