多节点中子节点判断问题

2009-10-22 08:08:22 110 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

大模型时代必备技能：3种向量量化技术对比（PQ/AQ/RVQ）与选型指南本文深入解析了大模型时代处理海量向量数据的关键技术——向量量化。通过对比Product Quantization (PQ)、Additive Quantization (AQ)和Residual Vector Quantization (RVQ)三种主流技术的核心原理、性能表现与适用场景，为工程师提供了从理论到实践的选型指南，旨在解决向量存储与检索的效率瓶颈，助力RAG等大模型应用高效落地。阅读详情

table是这样建立的
id int,uname nvarchar,parent int
parent是父节点的id，可以有多个根节点，根节点的parent值为0。
每个节点下面还会有子节点

怎样查询出某个节点的所有子节点？
怎样查询某个节点各级父节点？

...全文

R语言实战：非正态数据下的Scheirer–Ray–Hare检验全流程解析本文详细解析了在R语言中执行Scheirer–Ray–Hare检验的全流程。当数据不满足正态分布假设时，该非参数检验是传统双因素方差分析的有效替代方案。文章从原理、R环境配置、数据准备、代码实现、结果解读到事后比较与可视化，提供了完整的实战指南，帮助研究者稳健地分析多因素实验设计中的主效应和交互效应。阅读详情

aimeast

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

navy887 2009-10-22

打赏
举报

帮顶

华夏小卒 2009-10-22

打赏
举报

不写了

--小F-- 2009-10-22

打赏
举报

---也可以看水哥的

-->Title:Generating test data

-->Author:wufeng4552

-->Date :2009-09-30 08:52:38

set nocount on

if object_id('tb','U')is not null drop table tb

go

create table tb(ID int, ParentID int)

insert into tb select 1,0  

insert into tb select 2,1  

insert into tb select 3,1  

insert into tb select 4,2  

insert into tb select 5,3  

insert into tb select 6,5  

insert into tb select 7,6

-->Title:查找指定節點下的子結點

if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID

go

create function Uf_GetChildID(@ParentID int)

returns @t table(ID int)

as

begin

   insert @t select ID from tb where ParentID=@ParentID

   while @@rowcount<>0

   begin

      insert @t select a.ID from tb a inner join @t b

      on a.ParentID=b.id and 

      not exists(select 1 from @t where id=a.id)

   end 

return

end

go

select * from dbo.Uf_GetChildID(5)

/*

ID

-----------

6

7

*/

-->Title:查找指定節點的所有父結點

if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID

go

create function Uf_GetParentID(@ID int)

returns @t table(ParentID int)

as

begin

   insert @t select ParentID from tb where ID=@ID

   while @@rowcount!=0

   begin

     insert @t select a.ParentID from tb a inner join @t b

       on a.id=b.ParentID and 

       not exists(select 1 from @t where ParentID=a.ParentID)

   end

  return

end

go

select * from dbo.Uf_GetParentID(2)

/*

ParentID

-----------

1

0

*/

--小F-- 2009-10-22

打赏
举报

CREATE TABLE BOM(PID INT,ID INT)

INSERT INTO BOM SELECT 801,101

INSERT INTO BOM SELECT 801,102

INSERT INTO BOM SELECT 801,103

INSERT INTO BOM SELECT 801,601

INSERT INTO BOM SELECT 601,101

INSERT INTO BOM SELECT 601,105

INSERT INTO BOM SELECT 601,501

INSERT INTO BOM SELECT 501,106

INSERT INTO BOM SELECT 501,121

GO



CREATE FUNCTION F_GETROOT(@PID INT)

RETURNS INT

AS

BEGIN

    DECLARE @ID INT

    WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)

    BEGIN

        SET @ID=@PID

        SELECT @PID=PID FROM BOM WHERE ID=@ID

    END

    RETURN @PID

END

GO



SELECT PID=DBO.F_GETROOT(PID),ID FROM BOM

GO



/*

PID         ID

----------- ----------- 

801         101

801         102

801         103

801         601

801         101

801         105

801         501

801         106

801         121

*/





DROP FUNCTION F_GETROOT

DROP TABLE BOM

GO









--生成测试数据

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)

as

begin 

    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 bom_head=@bom_head)    

    insert into @a 

    select bom_child,number,products_attribute,@level 

    from BOM_1 

    where bom_head=@bom_head

    

    while exists(select 1 from @a where [level]=@level and products_attribute='生产')

    begin

        set @level=@level+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]=@level-1

    end

    

    insert into @t(bom,number) select bom,number from @a where products_attribute='采购'

    return

end

go





--执行调用，取父节点'A'一个标准配置分解的采购信息及数量

select * from dbo.f_stock('A')













--生成测试数据

create table BOM(ID INT,PID INT,MSG VARCHAR(1000))

insert into BOM select 1,0,NULL

insert into BOM select 2,1,NULL

insert into BOM select 3,1,NULL

insert into BOM select 4,2,NULL

insert into BOM select 5,3,NULL

insert into BOM select 6,5,NULL

insert into BOM select 7,6,NULL

go



--创建用户定义函数用于取每个父节点下子节点的采购配置信息

create function f_getChild(@ID VARCHAR(10))

returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)

as

begin

    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

    return

end

go



--执行查询

select ID from dbo.f_getChild(3)

go



--输出结果

/*

ID

----

5

6

7

*/



--删除测试数据

drop function f_getChild

drop 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)

as

begin

    declare @ret varchar(40)



    while exists(select 1 from BOM where ID=@ID and parentID<>0)

    begin

        select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')

        from

            BOM a,BOM b

        where

            a.ID=@ID and b.ID=a.parentID

    end

    

    set @ret=stuff(@ret,1,1,'')

    return @ret

end

go



--执行查询

select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM

go



--输出结果

/*

ID          parentID                                 

----------- ---------------------------------------- 

1           

2           1

3           1,2

4           1,2,3

5           1   

*/



--删除测试数据

drop function f_getParent

drop table BOM

go

多节点中子节点判断问题

相关推荐