mysql 父节点统计子节点下所有文档数量包括父节点下的文档数量

有两张表，一张是目录层级关系表 a 层级不定
另一张是目录与文档关系表 b
求父节点下面的子节点文档数量总和 包括父节点下面也有文档
A表
id name pid
1 -语文
2 -数学
3 -英语
4 -语文1 - 1
5 -数学1 - 2
6 -英语1 - 3
B表
id（目录id） name（文档名）
1 -- aa
4 -- bb
4 -- cc
2 -- dd
3 -- ee
3 -- ee1
6 -- ee2
我使用后结果对 就直接采纳
想要的结果数量为
语文：3
数学：1
英语：3

select name,(select count(*) from 表B where id=a.id ) from 表A a where pid is null

CREATE TABLE A表
(
id INT, name VARCHAR(50), pid int
)

CREATE TABLE B表
(
id INT, name VARCHAR(50)
)

INSERT INTO A表 VALUES(1 ,'语文',0)
INSERT INTO A表 VALUES(2, '数学',0)
INSERT INTO A表 VALUES(3, '英语',0)
INSERT INTO A表 VALUES(4, '语文1',  1)
INSERT INTO A表 VALUES(5, '数学1',  2)
INSERT INTO A表 VALUES(6, '英语1',  3)


INSERT INTO B表 values(1 , 'aa')
INSERT INTO B表 values(4 , 'bb')
INSERT INTO B表 values(4 , 'cc')
INSERT INTO B表 values(2 , 'dd')
INSERT INTO B表 values(3 , 'ee')
INSERT INTO B表 values(3 , 'ee1')
INSERT INTO B表 values(6 , 'ee2')

;WITH ct
AS
(
    SELECT *,name AS cname  FROM A表 WHERE ISNULL(pid,0)=0
    UNION ALL
    SELECT B.id,b.name,b.pid,cname  FROM ct a INNER  JOIN A表 B ON A.id=B.pid 
)
SELECT cname,COUNT(1) AS rst FROM ct a inner join B表 b ON b.id = a.id
GROUP BY cname


 

由于A表层级不确定,所以肯定要用递归。

如果是8.0版本的mysql,可以使用with递归

CREATE TABLE A表
(
id INT, name VARCHAR(50), pid int
);
 
CREATE TABLE B表
(
id INT, name VARCHAR(50)
)
 ;
INSERT INTO A表 VALUES(1 ,'语文',0);
INSERT INTO A表 VALUES(2, '数学',0);
INSERT INTO A表 VALUES(3, '英语',0);
INSERT INTO A表 VALUES(4, '语文1',  1);
INSERT INTO A表 VALUES(5, '数学1',  2);
INSERT INTO A表 VALUES(6, '英语1',  3);
 
 
INSERT INTO B表 values(1 , 'aa');
INSERT INTO B表 values(4 , 'bb');
INSERT INTO B表 values(4 , 'cc');
INSERT INTO B表 values(2 , 'dd');
INSERT INTO B表 values(3 , 'ee');
INSERT INTO B表 values(3 , 'ee1');
INSERT INTO B表 values(6 , 'ee2')
 
;
with recursive t as
(select a.*,1 level,a.name root_name from A表 a where a.pid=0
union all 
select a.*,level+1,t.name root_name from A表 a inner join t where a.pid=t.id)
select root_name,count(b.name) from t,B表 b 
where t.id=b.id group by root_name;

效果如下

建议在表结构中引入path，可以是id来体现或者增加一个一段，这样就可以一条sql语句直接查询出任意节点关键业务数据，这是典型的向上汇总需求，一般用于统计需求。由于细节文字表达不太方便，感兴趣可以私聊我给你解释原理