求一反向搜索BOM的SQL语句

Question

create table bom(ParentID int , ChildID  int)
insert into bom values(1,2)
insert into bom values(2,3)
insert into bom values(3,4)
insert into bom values(4,5)
insert into bom values(4,6)
insert into bom values(5,6)
go--查询指定节点及其所有父节点的函数
create function f_ParentID(@cid int) returns @t_level table(ParentID int)
as
begin
  insert into @t_level select @cid
  select @cid = ParentID from bom where ChildID = @cid 
  while @@ROWCOUNT > 0
  begin
    insert into @t_level select @cid select @cid = ParentID from bom where ChildID = @cid 
  end
  return
end
go--调用函数查询6及其所有父节点
select a.* from bom a , f_ParentID(6) b where a.ParentID = b.ParentID order by a.ParentID
--调用函数查询5及其所有父节点
select a.* from bom a , f_ParentID(2) b where a.ParentID = b.ParentID order by a.ParentID

Answer 1

/*
标题：查询指定节点及其所有子节点的函数
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go--调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 10 行）
*/--调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
002  001  广州市
004  002  天河区（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
003  001  深圳市
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇（所影响的行数为 7 行）
*/drop table tb
drop function f_cid
--查詢父結點
create table T(ID int,UP int)
insert T select 2,  1 
insert T select 3,  1 
insert T select 4,  1 
insert T select 5,  2 
insert T select 6,  2 
insert T select 7,  5 
insert T select 8,  5 
insert T select 9,  7 declare @ID int
set @ID=9
while exists(select 1 from T where ID=@ID and up<>1 )
select @ID=up from T where ID=@ID and up<>1
select @ID--函數
create function F_test(@ID int)
returns int
as
begin 
    declare @UPID int
    select @UPID=up from T where ID=@ID 
    if coalesce(@UPID,1)<>1
        return dbo.F_test(@UPID)
    return @ID         
    
endselect dbo.F_test(9)--
-- > 测试数据: @T
declare @T table (id int,up int)
insert into @T
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,2 union all
select 7,5 union all
select 8,5 union all
select 9,7declare @id int
set @id = 9
while @@rowcount > 0 select @id = up from @T where id = @id and up <> 1
select @id

Answer 2

-->参考邹建老大的.use   tempdb   
  go   
    
  --建立测试环境   
  Create   Table   表(料件編號   varchar(10),子料件   varchar(10))   
  --插入数据   
  insert   into   表   
  select   '100-A-1','500-A-C'   union   
  select   '100-A-1','500-A-B'   union   
  select   '100-A-1','500-A-D'   union   
  select   '500-A-C','600-A-F'   union   
  select   '500-A-C','600-A-D'   union   
  select   '500-A-B','600-D-D'   union   
  select   '500-A-B','600-D-7'   union   
  select   '500-A-D','700-F-1'   union   
  select   '500-A-D','700-F-4'   
  --select   *   from   表   
  --测试语句   
  go   
  create   function   f_cid(@子料件   varchar(10))   
  returns   @re   table(父项   varchar(10),[level]   int,sid   varchar(8000))   
  as   
  begin   
  declare   @l   int   
  set   @l=1   
  insert   @re   select   料件編號,@l,料件編號   
  from   表   a   
  where   子料件=@子料件   
  while   @@rowcount>0   
  begin   
  set   @l=@l+1   
  insert   @re   select   a.料件編號,@l,a.料件編號+'   ->   '+b.sid   
  from   表   a,@re   b   
  where   a.子料件=b.父项   and   b.[level]=@l-1   
  end   
  update   @re   set   level=@l-level   
  return   
  end   
  go   
  select   *   from   dbo.f_cid('700-F-4')     
  go   
    
  --删除测试环境   
  Drop   Table   表   
  drop   function   f_cid   
    
  /*   
  父项                   level               sid                             
  ----------   -----------   ------------------   
  500-A-D         2                       500-A-D   
  100-A-1         1                       100-A-1   ->   500-A-D   
    
  （所影响的行数为   2   行）   
  */   

Answer 3

3楼的给反了.应该是:
/*
标题：查询指定节点及其所有父节点的函数
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有父节点的函数
create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
as
begin
  insert into @t_level select @id
  select @id = pid from tb where id = @id and pid is not null
  while @@ROWCOUNT > 0
  begin
    insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null
  end
  return
end
go--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市（所影响的行数为 2 行）
*/--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市
007  003  宝安区
008  007  西乡镇（所影响的行数为 4 行）
*/drop table tb
drop function f_pid

Answer 4

CTE
table BOM 列1  ParentID int, 
列2  ChildID  int declare @id int;
set @id=10;
;WITH FUCK AS
(
SELECT * FROM dbo.BOM where childid=@id
union all
select * from dbo.BOM a join fuck as b on a.childid=b.parentid
)
select * from fuck

Answer 5

table BOM 列1  ParentID int, 
列2  ChildID  int declare @t table(id int)
insert into @t values(某节点ID）while(exists(select * from BOM where childid in (select id from @t) and parentid not in (select id from @t)))
  insert into @t select ParentID from BOM where childid in (select id from @t) and parentid not in (select id from @t)表@t就是所有的父ID

Answer 6

USE [MES]
GO
/****** Object:  UserDefinedFunction [dbo].[FIND_CID_BOM]    Script Date: 03/06/2009 11:57:14 ******/
SET QUOTED_IDENTIFIER ON
GO
--查询指定节点及其所有子节点的函数 
-- ================================================
/*
标题：查询指定节点及其所有子节点的函数
作者：北方男生(天南地北天涯浪子浪跡天涯，秋去冬來秋水伊人望穿秋水)
时间：2009-01-21
地点：广东東莞
*/
-- =================================================
--BOM_TYP
--BP_NO
--PARN_ITM
--PARN_LITM
--PARN_TYP
--PARN_DSC
--CHLD_ITM
--CHLD_LITM
--CHLD_DSC
--QYT
--ECO_NO
--CHLD_STK_TYP--31010555201
--15140016000
--15110661000
--select top 1* from sc_xlrbb where lh='21000138201'
--SELECT A.* FROM RES_BOM_LH A,FIND_CID_BOM('15140016000') B WHERE A.PARN_LITM=B.PARN_LITM ORDER BY A.PARN_LITM
--SELECT * FROM Find_LH_BOM('15140016000')
--SELECT * FROM Get_ChldLH_BOM('15140016000')
--select * from (select parn_litm  from res_bom_lh union select chld_litm from res_bom_lh)as t where parn_litm='10320403200'ALTER FUNCTION [dbo].[FIND_CID_BOM](@ID NVARCHAR(50)) RETURNS @T_LEVEL TABLE(PARN_LITM NVARCHAR(50),LEVEL INT)
AS
BEGIN
  DECLARE @LEVEL INT
  SET @LEVEL=1
  INSERT INTO @T_LEVEL SELECT @ID,@LEVEL
  WHILE @@ROWCOUNT>0
  BEGIN
SET @LEVEL=@LEVEL+1
INSERT INTO @T_LEVEL SELECT A.PARN_LITM,@LEVEL
FROM RES_BOM_LH A,@T_LEVEL B
WHERE A.CHLD_LITM=B.PARN_LITM AND B.LEVEL=@LEVEL-1
  END
  RETURN
END

Answer 7

USE [MES]
GO
/****** Object:  UserDefinedFunction [dbo].[FIND_PID_BOM]    Script Date: 03/06/2009 11:58:46 ******/
SET QUOTED_IDENTIFIER ON
GO
--查询指定节点及其所有父节点的函数
-- ================================================
/*
标题：查询指定节点及其所有父节点的函数
作者：北方男生(天南地北天涯浪子浪跡天涯，秋去冬來秋水伊人望穿秋水)
时间：2009-01-16
地点：广东東莞
*/
-- =================================================
--21000131101
--SELECT A.* FROM RES_BOM_LH A,FIND_PID_BOM('31010555201') B WHERE A.PARN_LITM=B.PARN_LITM ORDER BY A.PARN_LITM
ALTER FUNCTION [dbo].[FIND_PID_BOM](@ID NVARCHAR(50)) RETURNS @T_LEVEL TABLE (PARN_LITM NVARCHAR(50))
AS
BEGIN
INSERT INTO @T_LEVEL SELECT @ID
SELECT @ID=CHLD_LITM FROM RES_BOM_LH WHERE PARN_LITM=@ID AND CHLD_LITM IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT INTO @T_LEVEL SELECT @ID SELECT @ID=CHLD_LITM FROM RES_BOM_LH WHERE PARN_LITM=@ID AND CHLD_LITM IS NOT NULL
END
RETURN
END

Answer 8

USE [MES]
GO
/****** Object:  UserDefinedFunction [dbo].[Get_ChldLH_BOM]    Script Date: 03/06/2009 11:59:58 ******/
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Get_ChldLH_BOM] (@FindLH nvarchar(50))
RETURNS @retPLExpand TABLE (LH nvarchar(50),LHDES nvarchar(500))
AS
BEGIN
   DECLARE @RowsAdded int
   DECLARE @PLExpand TABLE (PARN_DSC nvarchar(100),PARN_LITM nvarchar(50),CHLD_DSC nvarchar(100),CHLD_LITM nvarchar(50),
   processed tinyint default 0)  INSERT @PLExpand
  SELECT PARN_DSC,PARN_LITM,CHLD_DSC,CHLD_LITM,0
  FROM RES_BOM_LH 
  WHERE PARN_LITM=@FindLH
  SET @RowsAdded=@@rowcount
  WHILE @RowsAdded>0
  BEGIN
      UPDATE @PLExpand
      SET processed = 1
      WHERE processed = 0
      INSERT @PLExpand
      SELECT PARN_DSC,PARN_LITM,CHLD_DSC,CHLD_LITM,0
      FROM RES_BOM_LH 
      WHERE ltrim(PARN_LITM) in (select ltrim(CHLD_LITM) from @PLExpand where processed=1)
      SET @RowsAdded=@@rowcount
      UPDATE @PLExpand
      SET processed=2
      WHERE processed=1
  END
  INSERT @retPLExpand
  select PARN_LITM as LH,max(PARN_DSC) as LHDES from(
  select PARN_LITM,PARN_DSC from @PLExpand union
  select CHLD_LITM as PARN_LITM,CHLD_DSC as PARN_DSC from @PLExpand)a group by PARN_LITM
  RETURN
END
 
 

Answer 9

USE [MES]
GO
/****** Object:  UserDefinedFunction [dbo].[Find_LH_BOM]    Script Date: 03/06/2009 12:02:04 ******/
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Find_LH_BOM] (@FindID nvarchar(50))
RETURNS @retPLExpand TABLE (PARN_TYP nvarchar(50),PARN_DSC nvarchar(100),PARN_LITM nvarchar(50),CHLD_DSC nvarchar(100), CHLD_LITM nvarchar(50),
   QYT INT NULL
   )
AS
BEGIN
   DECLARE @RowsAdded int
   DECLARE @PLExpand TABLE (PARN_TYP nvarchar(50),PARN_DSC nvarchar(100),PARN_LITM nvarchar(50),CHLD_DSC nvarchar(100),CHLD_LITM nvarchar(50),
   QYT INT NULL,
   processed tinyint default 0)  INSERT @PLExpand
  SELECT PARN_TYP,PARN_DSC,PARN_LITM,CHLD_DSC,CHLD_LITM,QYT,0
  FROM RES_BOM_LH 
  WHERE PARN_LITM=@FindID
  SET @RowsAdded=@@rowcount
  WHILE @RowsAdded>0
  BEGIN
      UPDATE @PLExpand
      SET processed = 1
      WHERE processed = 0
      INSERT @PLExpand
      SELECT PARN_TYP,PARN_DSC,PARN_LITM,CHLD_DSC,CHLD_LITM,QYT,0
      FROM RES_BOM_LH 
      WHERE ltrim(PARN_LITM) in (select ltrim(CHLD_LITM) from @PLExpand where processed=1)
      SET @RowsAdded=@@rowcount
      UPDATE @PLExpand
      SET processed=2
      WHERE processed=1
  END
  INSERT @retPLExpand
  SELECT PARN_TYP,PARN_DSC,PARN_LITM,CHLD_DSC,CHLD_LITM,QYT
  FROM @PLExpand
  RETURN
END
 
 
select * from find_lh_bom('31010644101')

Answer 10

XScroll V2                                         RS,XSCROLL V2,PS2,BLACK,G5,GM!                                                                       31010644101                                        RS,上下隔板(475X402)                                                                                     12110034000                                        0

Answer 11

对于BOM结构的，建议再定义另外一个字段来建立关系，假设：叫BSID，
table BOM :列1:parentID int ,列2：childID,列3：BSID
数据1:-1,1,001
数据2：1,2,001001
数据3：2,3,001001001
这样就可以通过BSID,查找数据1所有的子节点 ，where BOM.BSID like '001'+'%'
查找数据3所有的父节点（递归）,where '001001001' like BOM.BSID+'%'