表结构:(ID,parentid)数据示例:
id parentid
1 0
2 1
3 1
4 0
5 2
6 5
7 3想得到的结果:
得到所有顶级节点的子节点个数(顶级节点定义为 parentid为0的节点);就如上示例数据来讲,最终结果应该是
id childcount
1 5
4 0搜索过论坛,还是没有思路,请各位帮忙。如果表结构设计不合理,也可以提出建议。多谢
id parentid
1 0
2 1
3 1
4 0
5 2
6 5
7 3想得到的结果:
得到所有顶级节点的子节点个数(顶级节点定义为 parentid为0的节点);就如上示例数据来讲,最终结果应该是
id childcount
1 5
4 0搜索过论坛,还是没有思路,请各位帮忙。如果表结构设计不合理,也可以提出建议。多谢
drop table if exists test;create table test(id INT,parentid INT);insert test select
1, 0 UNION ALL SELECT
2, 1 UNION ALL SELECT
3, 1 UNION ALL SELECT
4, 0 UNION ALL SELECT
5, 2 UNION ALL SELECT
6, 5 UNION ALL SELECT
7, 3 ;
godelimiter $$ create procedure usp_ser(in idd varchar(100))begindeclare lev int;set lev=1;drop table if exists tmp1;CREATE TABLE tmp1(id INT,parentid INT ,levv INT,ppath VARCHAR(1000));
INSERT tmp1 SELECT *,lev,id FROM test WHERE parentid=idd;
while row_count()>0do
set lev=lev+1;insert tmp1 select t.*,lev,concat(a.ppath,t.id) from test t join tmp1 a on t.parentid=a.id AND levv=LEV-1;
end while ;SELECT LEFT(ppath,1) AS id ,count(*)-1 AS childcount FROM tmp1 GROUP BY LEFT(ppath,1) ;end;$$ delimiter ; call usp_ser(0);
/*
+------+------------+
| id | childcount |
+------+------------+
| 1 | 5 |
| 4 | 0 |*/
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
===》
这还不简单?改成这样就好了
SELECT substring_index(ppath,',',1) AS id ,count(*)-1 AS childcount
FROM tmp1 GROUP BY substring_index(ppath,',',1) ;