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

我现在有两个表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里很难看。有没有更好的写法?

4个回答

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

qq_39348131
qq_39348131 回复u012878278: 厉害了大佬
一年多之前 回复
u012878278
我爱某某 回复qq_39348131: 已按你的需求修改
一年多之前 回复
qq_39348131
qq_39348131 需求是:查出来同一id只有一条记录,里有三个栏位分别存三个状态的个数。你的sql是 有三条记录,只有一个栏位分别存每个状态的个数
一年多之前 回复

首先我觉得应该是要用外连接
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

qq_39348131
qq_39348131 需求是:查出来同一id只有一条记录,里有三个栏位分别存三个状态的个数。你的sql是 有三条记录,只有一个栏位分别存每个状态的个数
一年多之前 回复

方案思路:
第一步: 数据量评估,如果数据量大,请建立索引-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

falali19880120
千秋无痕 回复qq_39348131: 已增加方案,你看下
一年多之前 回复
qq_39348131
qq_39348131 需求是:查出来同一id只有一条记录,里有三个栏位分别存三个状态的个数。你的sql是 有三条记录,只有一个栏位分别存每个状态的个数
一年多之前 回复
falali19880120
千秋无痕 回复qq_39348131: case when 是用来消除null的,把null的变为0,也可以用ifnull
一年多之前 回复
qq_39348131
qq_39348131 能给出具体的sql吗,case when不太会用
一年多之前 回复

我晚了一步 还是给你吧

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问