从根节点找所有子节点

use tempdb go create table project ( projectid int, [name] varchar(50) ) create table project_special ( projectid int, specialid int, tyoeofid int ) create table special ( specialid int, parentid int, [name] varchar(50) ) insert project values(1,'水力项目') insert project values(2,'水力项目一期二小节') insert project values(3,'火力项目') insert project values(4,'火力项目一期二小节') insert project values(5,'火力项目三期五小节') insert special values(1,null,'水力') insert special values(2,null,'电力') insert special values(3,null,'火力') insert special values(4,null,'大型') insert special values(5,null,'开发') insert special values(6,1,'水力一期') insert special values(7,1,'水力二期') insert special values(8,3,'火力一期') insert special values(9,3,'火力二期') insert project_special values(1,6,1) insert project_special values(3,8,3) insert project_special values(2,7,1) insert project_special values(4,9,3) insert project_special values(5,9,3) select * from project select * From project_special select * from special --select tyoeofid,COUNT(*) from project_special group by tyoeofid declare @spid int declare @parentid int set @parentid=8 while(@parentid is not null) begin select @spid=specialid,@parentid=parentid from special where specialid=@parentid end select @spid,@parentid declare @tmp table ( specialid int, parentid int, typeofid int ) insert into @tmp select specialid,parentid,specialid from special where parentid is null while @@rowcount>0 begin insert into @tmp select a.specialid,parentid,b.typeofid from special a inner join a.id from menu a inner join @re b on a.parent=b.id where a.id not in(select id from @re) end with tmp(id,pid,typeid) as ( select specialid,parentid,specialid from special where parentid is null union all select b.specialid,b.parentid,a.typeid from tmp a inner join special b on a.id = b.parentid ) select a.typeid,c.[name],COUNT(*) from tmp a inner join project_special b on a.id=b.specialid inner join special c on a.typeid=c.specialid group by a.typeid,c.[name] --select * from special select * from project_special select b.specialid,b.parentid from special a inner join special b on a.specialid = b.parentid