如何根据这个需求编写mysql，保证简洁和效率

Question

我现在有两个表parent和child，根据parent.id等于child.parentId一对多关联。
child表有一字段status，取值 0或1或2。
现在我想要一起查询出每条parent的信息以及该条对应的child状态status分别为0、1、2的记录个数。
这个个数我不知道怎么取，比如下面是取parent信息和状态为0的child个数，但只能取到一种情况，不能同时取到三种状态的个数。
SELECT p. * , count ( * )
FROM parent p,child c
WHERE c.parentid =p.id
AND c.status= 0
GROUP BY c.parentid;
要同时取到三种状态就要继续加联表
SELECT * FROM
(SELECT p. * , count ( * ) c1 ,p.ID aid
FROM parent p,child c
WHERE c.parentid =p.id
AND c.STATUS = 0
GROUP BY c.parentid) a ,
(SELECT count ( * ) c2 ,p.ID bid
FROM parent p,child c
WHERE c.parentid =p.id
AND c.STATUS = 1
GROUP BY c.parentid) b ,
(SELECT count ( * ) c3 ,p.ID cid
FROM parent p,child c
WHERE c.parentid =p.id
AND c.STATUS = 2
GROUP BY c.parentid) c
WHERE a.aid = b.bid AND c.cid = a.aid
这样sql会很长且低效。并且现在只有三个状态，要是多了更长，后面会写在mybatis的xml里很难看。有没有更好的写法？

Answer 1

select id,sum(child_cnt0),sum(child_cnt1),sum(child_cnt2)
from(select p.id, case when c.status = 0 then count(*) end as child_cnt0
, case when c.status = 1 then count(*) end as child_cnt1
, case when c.status = 2 then count(*) end as child_cnt2
from parent p
left join child c
on p.id = c.parentid
where c.status in (0,1,2)
group by p.id , c.status)temp
group by temp.id
order by temp.id