我现在有两个表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里很难看。有没有更好的写法？
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
首先我觉得应该是要用外连接
SELECT
p.*,
d.nCount,
d.status
FROM
parent p
LEFT JOIN (
SELECT
COUNT (*) as nCount,
c.parentid,
c.status
FROM
child c
GROUP BY c.parentid,c.status
) d ON d.parentid = p.id
方案思路：
第一步： 数据量评估，如果数据量大，请建立索引-child.parentid, child state，加快连接速度；
第一步： 用左连接，parent为主表，child为副，parent p left join child c on c.parentid = p.id group by p.id, c.state
第三步： 在字段中用case when state
1: select p.id, c.state,ifnull(count(c.state),0) from parent p
left join child c on p.id = c.parentid
group by p.id, c.state
2:
select p.id,
sum(case c.state when 0 then 1 else 0 end state0) state0Count，
sum(case c.state when 1 then 1 else 0 end state1) state1Count,
..............
from parent p
left join child c on p.id = c.parentid
group by p.id, c.state
我晚了一步 还是给你吧
SELECT p.*,
COUNT(CASE WHEN c.status=0 THEN '' END) '0',
COUNT(CASE WHEN c.status=1 THEN '' END) '1',
COUNT(CASE WHEN c.status=2 THEN '' END) '2'
FROM parent p,child c WHERE p.id=c.parentid GROUP BY c.parentid;
SELECT p.*,
(SELECT COUNT(*) FROM child WHERE status=0 AND parentid=p.id) AS 'status 0',
(SELECT COUNT(*) FROM child WHERE status=1 AND parentid=p.id) AS 'status 1',
(SELECT COUNT(*) FROM child WHERE status=2 AND parentid=p.id) AS 'status 2'
FROM parent p GROUP BY p.id