问一条SQl语句实现解决办法

--生成测试数据create table BOM(ID INT,PID INT,MSG VARCHAR(1000))insert into BOM select 1,0,NULLinsert into BOM select 2,1,NULLinsert into BOM select 3,1,NULLinsert into BOM select 4,2,NULLinsert into BOM select 5,3,NULLinsert into BOM select 6,5,NULLinsert into BOM select 7,6,NULLgo--创建用户定义函数用于取每个父节点下子节点的采购配置信息create function f_getChild(@ID VARCHAR(10))returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)asbegin    declare @i int    set @i = 1    insert into @t select ID,PID,@i from BOM where PID = @ID        while @@rowcount<>0    begin        set @i = @i + 1                insert into @t         select             a.ID,a.PID,@i         from             BOM a,@t b         where             a.PID=b.ID and b.Level = @i-1    end    returnendgo--执行查询select ID from dbo.f_getChild(3)go--输出结果/*ID----567*/--删除测试数据drop function f_getChilddrop table BOM
------解决方案--------------------
部门表 Department 字段( ID ,DepartmentName , ParentDepartment)  
员工表 Employee   字段( ID ,EmployeeName , ParentID )  
SQL code
create function wsp(@ID VARCHAR(10))returns varchar(500)asbegin    declare @i int    declare @t table(ID int,ParentDepartment int,Level INT)    set @i = 1    insert into @t select ID,ParentDepartment,@i from Department where ID = @ID    while @@rowcount<>0    begin        set @i = @i + 1        insert into @t select a.ID,a.ParentDepartment,@i         from Department a,@t b where a.ParentDepartment=b.ID and b.Level = @i-1    end    declare @sql varchar(500)    select @sql=isnull(@sql+',','')+ltrim(id) from @t     return @sqlendgo--调用：select * from Employee where charindex(','+ltrim(ParentID)+',',','+dbo.wsp(部门id)+',')>0
------解决方案--------------------
SQL code
--测试数据CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))INSERT tb SELECT '001',NULL ,'山东省'UNION ALL SELECT '002','001','烟台市'UNION ALL SELECT '004','002','招远市'UNION ALL SELECT '003','001','青岛市'UNION ALL SELECT '005',NULL ,'四会市'UNION ALL SELECT '006','005','清远市'UNION ALL SELECT '007','006','小分市'GO--查询指定节点及其所有子节点的函数CREATE FUNCTION f_Cid(@ID char(3))RETURNS @t_Level TABLE(ID char(3),Level int)ASBEGIN    DECLARE @Level int    SET @Level=1    INSERT @t_Level SELECT @ID,@Level    WHILE @@ROWCOUNT>0    BEGIN        SET @[email protected]+1        INSERT @t_Level SELECT a.ID,@Level        FROM tb a,@t_Level b        WHERE a.PID=b.ID            AND [email protected]    END    RETURNENDGO--调用函数查询002及其所有子节点SELECT a.*FROM tb a,f_Cid('002') bWHERE a.ID=b.ID/*--结果ID   PID  Name       ------ ------- ---------- 002  001  烟台市004  002  招远市--*/
------解决方案--------------------
SQL code
create table tb(id int, name varchar(10), pid int, px int)insert into tb values(0 , '栏目分类', 0 , 1)insert into tb values(1 , '动物' , 0 , 1) insert into tb values(2 , '视频' , 0 , 2) insert into tb values(3 , '老虎' , 1 , 1) insert into tb values(4 , '狮子' , 1 , 2) insert into tb values(5 , '搞笑' , 2 , 1) go--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) AS BEGIN   DECLARE @Level int   SET @Level=1   INSERT @t_Level SELECT @ID,@Level   WHILE @@ROWCOUNT>0   BEGIN     SET @[email protected]+1     INSERT @t_Level SELECT a.ID,@Level     FROM tb a,@t_Level b     WHERE a.PID=b.ID     AND [email protected]   END   RETURN END GO --调用函数查询id = 1及其所有子节点 SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID /*id          name       pid         px          ----------- ---------- ----------- ----------- 1           动物         0           13           老虎         1           14           狮子         1           2（所影响的行数为 3 行）*/drop table tbdrop function dbo.f_cid