SQL 树状查询

CREATE TABLE BOM(PID INT,ID INT)INSERT INTO BOM SELECT 801,101INSERT INTO BOM SELECT 801,102INSERT INTO BOM SELECT 801,103INSERT INTO BOM SELECT 801,601INSERT INTO BOM SELECT 601,101INSERT INTO BOM SELECT 601,105INSERT INTO BOM SELECT 601,501INSERT INTO BOM SELECT 501,106INSERT INTO BOM SELECT 501,121GOCREATE FUNCTION F_GETROOT(@PID INT)RETURNS INTASBEGIN    DECLARE @ID INT    WHILE EXISTS(SELECT 1 FROM BOM WHERE [email protected])    BEGIN        SET @[email protected]        SELECT @PID=PID FROM BOM WHERE [email protected]    END    RETURN @PIDENDGOSELECT PID=DBO.F_GETROOT(PID),ID FROM BOMGO/*PID         ID----------- ----------- 801         101801         102801         103801         601801         101801         105801         501801         106801         121*/DROP FUNCTION F_GETROOTDROP TABLE BOMGO--生成测试数据create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute  varchar(20))insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'insert into BOM_1 select 3 ,'A2','A3',3,'生产'insert into BOM_1 select 4 ,'A2','A4',2,'采购'insert into BOM_1 select 5 ,'A3','A5',2,'采购'insert into BOM_1 select 6 ,'A3','A6',1,'采购'insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'insert into BOM_1 select 9 ,'B2','B3',3,'生产'insert into BOM_1 select 10,'B2','B4',2,'采购'insert into BOM_1 select 11,'B3','B5',2,'采购'insert into BOM_1 select 12,'B3','B6',2,'采购'go   --创建用户定义函数，用于取每个父节点下子节点的采购配置信息create function f_stock(@bom_head varchar(20))returns @t table(bom varchar(20),number int)asbegin     declare @level int    declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int)    set @level=1    if exists(select 1 from BOM_1 where [email protected]_head)        insert into @a     select bom_child,number,products_attribute,@level     from BOM_1     where [email protected]_head        while exists(select 1 from @a where [level][email protected] and products_attribute='生产')    begin        set @[email protected]+1        insert into @a(bom,number,products_attribute,[level])        select a.bom_child,a.number,a.products_attribute,@level         from BOM_1 a,@a b        where a.bom_head=b.bom and b.[level][email protected]    end        insert into @t(bom,number) select bom,number from @a where products_attribute='采购'    returnendgo--执行调用，取父节点'A'一个标准配置分解的采购信息及数量select * from dbo.f_stock('A')--生成测试数据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创建用户定义函数,每个子节点de父节点的信息--生成测试数据create table BOM(ID int,parentID int,sClassName varchar(10))insert into BOM values(1,0,'1111'      )insert into BOM values(2,1,'1111_1'    )insert into BOM values(3,2,'1111-1-1'  )insert into BOM values(4,3,'1111-1-1-1') insert into BOM values(5,1,'1111-2'    )go--创建用户定义函数,每个子节点de父节点的信息create function f_getParent(@ID int)returns varchar(40)asbegin    declare @ret varchar(40)    while exists(select 1 from BOM where [email protected] and parentID<>0)    begin        select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')        from            BOM a,BOM b        where            [email protected] and b.ID=a.parentID    end        set @ret=stuff(@ret,1,1,'')    return @retendgo--执行查询select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOMgo--输出结果/*ID          parentID                                 ----------- ---------------------------------------- 1           2           13           1,24           1,2,35           1   */--删除测试数据drop function f_getParentdrop table BOMgo