mysql - 选择group_concat()包含字符的记录

Question

如何选择仅包含 0 的 mystatus 字段

select c.id,count(*),
       group_concat(distinct(r.status not in ('Done','None'))) as mystatus
from cases c inner join reports r 
on (c.id = r.parent_id and r.parent_type = 'Cases' and r.deleted = 0)
where c.deleted = 0 
and c. status <> 'Late'
group by c.id

结果看起来像这样，但我只想选择包含 0 的 mystatus。

ID  count(*)  mystatus
A   1         0
B   7         0,1
C   2         0

应该选择记录ID A和C

Answer 1

添加 HAVING 子句。

select c.id,count(*),
       group_concat(distinct(r.status not in ('Done','None'))) as mystatus
from cases c inner join reports r 
on (c.id = r.parent_id and r.parent_type = 'Cases' and r.deleted = 0)
where c.deleted = 0 
and c. status <> 'Late'
group by c.id
having mystatus = '0'