;with t as ( select id,pid,id p from tb where pid is null union all select tb.id,tb.pid, t.p from tb, t where t.id=tb.pid ) select top 1 p,count(1) cnt from t group by p order by cnt desc;
你说的子节点仅指下一级?还是所有?如果是下一级,好办. select top 1 pid from tb group by pid order by count(1) desc
仅一级的话,统计下pid,找出最多的那一个。
是数量最多的pid吗?本人也刚开始学,写的不标准,不简便还望指出。select pid from test group by pid having count(*) >= all(select count(*) from test group by pid)
[code=SQ]L Create table #t(id integer,pid integer ,name Nvarchar(20))Insert Into #t Select 004 id,002 pid,N'栾川县' name Union Select 007 id,004 pid,N'冷水乡' name Union Select 008 id,004 pid,N'叫河乡' name Union Select 009 id,008 pid,N'A村' name Union Select 010 id,008 pid,N'B村' name Select #t.Pid,Count(1) From #t Group by #t.Pid Having Count(1) = ( Select Max(SNum) From ( Select #t.Pid,Count(1) SNum From #t Group by #t.Pid )t )[/code]
;with t as (
select id,pid,id p from tb where pid is null
union all
select tb.id,tb.pid, t.p from tb, t where t.id=tb.pid
)
select top 1 p,count(1) cnt from t group by p order by cnt desc;
select top 1 pid from tb group by pid order by count(1) desc
group by pid
having count(*) >= all(select count(*) from test group by pid)
---- ---- ----------
001 000 栾川县
002 000 冷水乡
003 000 叫河乡
004 001 A村
005 002 B村
006 005 A村
007 003 B村
问题表达有点问题,不好意思,各位
pid是父节点,现在就要查找pid为000,也就是根节点,中哪个的子节点最多,
注:节点为000的子节点个数不确定,而且子节点是无限的节点
Create table #t(id integer,pid integer ,name Nvarchar(20))Insert Into #t
Select 004 id,002 pid,N'栾川县' name Union
Select 007 id,004 pid,N'冷水乡' name Union
Select 008 id,004 pid,N'叫河乡' name Union
Select 009 id,008 pid,N'A村' name Union
Select 010 id,008 pid,N'B村' name Select #t.Pid,Count(1)
From #t
Group by #t.Pid
Having Count(1) =
(
Select Max(SNum)
From
(
Select #t.Pid,Count(1) SNum
From #t
Group by #t.Pid
)t
)[/code]