mssql 树形结构查询(动态生成层级字段)

时间:2022-08-08 00:28:52
现有如下结构的数据:

物料编号 成本1 成本2 上级编号
A                 100         100
B1                 100         100   A
B2                 100         100         A
C1                 100         100  B1
C2                 100         100  B1
C3                 100         100  B2
C4                 100         100  B2

如何查询得到如下结构的结果:

物料编号1 物料编号2 物料编号3 成本1 成本2
A                                                100         100
A               B1                                100         100
A                 B1                 C1                100          100
A                 B1                 C2                100         100
A                 B2                                100         100
A                 B2                 C3                100         100
A                 B2                 C4                100         100

tsql 语句或存储过程都可以。

难点:物料编号1,物料编号2,物料编号3 是根据数据动态生成的。

11 个解决方案

#1


层级字段不确定是吗?还是就是3层?
最后结果如果是这样好处理点,能满足要求吗?
A,B2,C4         100         100

#2




if object_id('tempdb..#Tmp_iBomDetTbl') is not null
drop table #Tmp_iBomDetTbl

CREATE TABLE #Tmp_iBomDetTbl (
Detail_ID int identity(1,1),
SubItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
ParentItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
cost2 int)

Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)

--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom

Create   TABLE  #Tmp_ExpandBom
(
Level_No  int,
Level_Desc varchar(100) COLLATE Chinese_*_Stroke_CI_AS NULL,
Item_No   varchar(50)COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
Cost2 int,
xPath   varchar(1000) COLLATE Chinese_*_Stroke_CI_AS NULL
)   


DECLARE @Level_No int   
SET @Level_No=1   

--先加入最上層的一條記錄,
 INSERT   #Tmp_ExpandBom   (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
 Select  Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
 From   #Tmp_iBomDetTbl   a   
 Where  Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
  
    
  WHILE   @@rowcount>0    --根據最上層的一條記錄依次找出下一級數據
BEGIN   
  SET   @Level_No=@Level_No+1   
  INSERT   into #Tmp_ExpandBom  (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)

Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
 Select  
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)   
  From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
    Where y.Level_No=@Level_No-1  
and  x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
 From  #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No 
 Where b.Level_No=@Level_No-1  
) a
  End


Select xPath,Cost1,Cost2 From #Tmp_ExpandBom


#3


引用 1 楼 sinat_28984567 的回复:
层级字段不确定是吗?还是就是3层?
最后结果如果是这样好处理点,能满足要求吗?
A,B2,C4         100         100




层级是不确定的,完全根据数据生成。

#4


引用 2 楼 appetizing_fish1 的回复:


if object_id('tempdb..#Tmp_iBomDetTbl') is not null
drop table #Tmp_iBomDetTbl

CREATE TABLE #Tmp_iBomDetTbl (
Detail_ID int identity(1,1),
SubItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
ParentItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
cost2 int)

Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)

--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom

Create   TABLE  #Tmp_ExpandBom
(
Level_No  int,
Level_Desc varchar(100) COLLATE Chinese_*_Stroke_CI_AS NULL,
Item_No   varchar(50)COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
Cost2 int,
xPath   varchar(1000) COLLATE Chinese_*_Stroke_CI_AS NULL
)   


DECLARE @Level_No int   
SET @Level_No=1   

--先加入最上層的一條記錄,
 INSERT   #Tmp_ExpandBom   (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
 Select  Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
 From   #Tmp_iBomDetTbl   a   
 Where  Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
  
    
  WHILE   @@rowcount>0    --根據最上層的一條記錄依次找出下一級數據
BEGIN   
  SET   @Level_No=@Level_No+1   
  INSERT   into #Tmp_ExpandBom  (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)

Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
 Select  
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)   
  From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
    Where y.Level_No=@Level_No-1  
and  x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
 From  #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No 
 Where b.Level_No=@Level_No-1  
) a
  End


Select xPath,Cost1,Cost2 From #Tmp_ExpandBom




dbo.IntToStr()  函数提供一下?

#5


引用 4 楼 backbone601 的回复:
Quote: 引用 2 楼 appetizing_fish1 的回复:



if object_id('tempdb..#Tmp_iBomDetTbl') is not null
drop table #Tmp_iBomDetTbl

CREATE TABLE #Tmp_iBomDetTbl (
Detail_ID int identity(1,1),
SubItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
ParentItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
cost2 int)

Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)

--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom

Create   TABLE  #Tmp_ExpandBom
(
Level_No  int,
Level_Desc varchar(100) COLLATE Chinese_*_Stroke_CI_AS NULL,
Item_No   varchar(50)COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
Cost2 int,
xPath   varchar(1000) COLLATE Chinese_*_Stroke_CI_AS NULL
)   


DECLARE @Level_No int   
SET @Level_No=1   

--先加入最上層的一條記錄,
 INSERT   #Tmp_ExpandBom   (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
 Select  Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
 From   #Tmp_iBomDetTbl   a   
 Where  Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
  
    
  WHILE   @@rowcount>0    --根據最上層的一條記錄依次找出下一級數據
BEGIN   
  SET   @Level_No=@Level_No+1   
  INSERT   into #Tmp_ExpandBom  (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)

Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
 Select  
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)   
  From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
    Where y.Level_No=@Level_No-1  
and  x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
 From  #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No 
 Where b.Level_No=@Level_No-1  
) a
  End


Select xPath,Cost1,Cost2 From #Tmp_ExpandBom




dbo.IntToStr()  函数提供一下?


Create FUNCTION [dbo].[IntToStr]
(@IntValue int,@Lenth int)
RETURNS Nvarchar(20)
AS
BEGIN
Return REPLICATE('0',@Lenth-len(ltrim(rtrim(str( @IntValue))))) + ltrim(rtrim(str(@IntValue)))
END

#6


@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

#7


借用2楼的数据。

WITH CTE1
AS
(SELECT *,ParentItem_No AS NO1,SubItem_No AS NO2,CAST('' AS VARCHAR(50)) AS NO3 ,1 AS LEVEL 
 FROM #A A
 WHERE EXISTS (SELECT 1 FROM #A WHERE A.SubItem_No=ParentItem_No)
       AND EXISTS (SELECT 1 FROM #A WHERE A.ParentItem_No=SubItem_No)
 UNION ALL
 SELECT B.*,B.SubItem_No,CAST('' AS VARCHAR(50)),CAST('' AS VARCHAR(50)),C.LEVEL+1
 FROM #A B 
 JOIN CTE1 C ON B.SubItem_No=C.ParentItem_No
 WHERE C.LEVEL>=1
 UNION ALL
 SELECT D.*,E.NO1,E.NO2,CAST(D.SubItem_No AS VARCHAR(50)),E.LEVEL-1
 FROM #A D 
 JOIN CTE1 E ON D.ParentItem_No=E.SubItem_No
 WHERE E.LEVEL<=1)
 
 SELECT NO1,NO2,NO3,Cost1,cost2,LEVEL FROM CTE1

#8


@rink_1 

提示 在递归查询 "CTE1" 的列 "NO3" 中,定位点类型和递归部分的类型不匹配。

另外:No1 No2 No3 也不是固定的,如果数据有四层的话,需要自动产生四个字段。

#9


引用 6 楼 backbone601 的回复:
@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

接上这些代码


DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(50)
DECLARE @Item_No varchar(50)

DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1

While @minLevel_No is not null
       Begin
 
              Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
              
              SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
              
              
              SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
      exec (@sql)
      set @sql=''
      
      SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where Level_No>'+cast(@minLevel_No-1 as varchar)

               exec (@sql)
               
               Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
       
       End
       
Select * From   #Tmp_ExpandBom     

#10


引用 9 楼 appetizing_fish1 的回复:
Quote: 引用 6 楼 backbone601 的回复:

@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

接上这些代码


DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(50)
DECLARE @Item_No varchar(50)

DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1

While @minLevel_No is not null
       Begin
 
              Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
              
              SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
              
              
              SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
      exec (@sql)
      set @sql=''
      
      SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where Level_No>'+cast(@minLevel_No-1 as varchar)

               exec (@sql)
               
               Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
       
       End
       
Select * From   #Tmp_ExpandBom     





引用 9 楼 appetizing_fish1 的回复:
Quote: 引用 6 楼 backbone601 的回复:

@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

接上这些代码


DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(50)
DECLARE @Item_No varchar(50)

DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1

While @minLevel_No is not null
       Begin
 
              Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
              
              SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
              
              
              SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
      exec (@sql)
      set @sql=''
      
      SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where Level_No>'+cast(@minLevel_No-1 as varchar)

               exec (@sql)
               
               Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
       
       End
       
Select * From   #Tmp_ExpandBom     



上一个有误,用这个吧



DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50) 
DECLARE @Level_Desc varchar(100)
DECLARE @lenLevel_Desc int
DECLARE @Item_No varchar(50)
DECLARE @PrevLevel_No int
SET @PrevLevel_No=0
DECLARE @CurrLevel_No int
DECLARE @minDetail_ID int
Select @minDetail_ID=Min(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>1
While @minDetail_ID is not null
       Begin
               Select @Level_Desc=Level_Desc,@lenLevel_Desc=len(level_desc),@Item_No=Item_No,@CurrLevel_No=Level_No 
               From #Tmp_ExpandBom 
               Where Detail_ID=@minDetail_ID
               
               IF @CurrLevel_No>1 And @CurrLevel_No<>@PrevLevel_No
   Begin
  SET @PrevLevel_No=@CurrLevel_No
  SET @Title='Item'+CAST(@CurrLevel_No-1 as varchar)
  SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
  exec (@sql)                   
   End
   
SET @sql=''
   
     SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where substring(Level_Desc,1,'+cast(@lenLevel_Desc as varchar)+')='''+@Level_Desc+''''

               exec (@sql)    
   

  Select @minDetail_ID=MIN(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>@minDetail_ID
              
       End

Select * From #Tmp_ExpandBom        
  

#11


@appetizing_fish1

可以了,谢谢!

#1


层级字段不确定是吗?还是就是3层?
最后结果如果是这样好处理点,能满足要求吗?
A,B2,C4         100         100

#2




if object_id('tempdb..#Tmp_iBomDetTbl') is not null
drop table #Tmp_iBomDetTbl

CREATE TABLE #Tmp_iBomDetTbl (
Detail_ID int identity(1,1),
SubItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
ParentItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
cost2 int)

Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)

--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom

Create   TABLE  #Tmp_ExpandBom
(
Level_No  int,
Level_Desc varchar(100) COLLATE Chinese_*_Stroke_CI_AS NULL,
Item_No   varchar(50)COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
Cost2 int,
xPath   varchar(1000) COLLATE Chinese_*_Stroke_CI_AS NULL
)   


DECLARE @Level_No int   
SET @Level_No=1   

--先加入最上層的一條記錄,
 INSERT   #Tmp_ExpandBom   (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
 Select  Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
 From   #Tmp_iBomDetTbl   a   
 Where  Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
  
    
  WHILE   @@rowcount>0    --根據最上層的一條記錄依次找出下一級數據
BEGIN   
  SET   @Level_No=@Level_No+1   
  INSERT   into #Tmp_ExpandBom  (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)

Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
 Select  
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)   
  From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
    Where y.Level_No=@Level_No-1  
and  x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
 From  #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No 
 Where b.Level_No=@Level_No-1  
) a
  End


Select xPath,Cost1,Cost2 From #Tmp_ExpandBom


#3


引用 1 楼 sinat_28984567 的回复:
层级字段不确定是吗?还是就是3层?
最后结果如果是这样好处理点,能满足要求吗?
A,B2,C4         100         100




层级是不确定的,完全根据数据生成。

#4


引用 2 楼 appetizing_fish1 的回复:


if object_id('tempdb..#Tmp_iBomDetTbl') is not null
drop table #Tmp_iBomDetTbl

CREATE TABLE #Tmp_iBomDetTbl (
Detail_ID int identity(1,1),
SubItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
ParentItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
cost2 int)

Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)

--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom

Create   TABLE  #Tmp_ExpandBom
(
Level_No  int,
Level_Desc varchar(100) COLLATE Chinese_*_Stroke_CI_AS NULL,
Item_No   varchar(50)COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
Cost2 int,
xPath   varchar(1000) COLLATE Chinese_*_Stroke_CI_AS NULL
)   


DECLARE @Level_No int   
SET @Level_No=1   

--先加入最上層的一條記錄,
 INSERT   #Tmp_ExpandBom   (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
 Select  Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
 From   #Tmp_iBomDetTbl   a   
 Where  Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
  
    
  WHILE   @@rowcount>0    --根據最上層的一條記錄依次找出下一級數據
BEGIN   
  SET   @Level_No=@Level_No+1   
  INSERT   into #Tmp_ExpandBom  (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)

Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
 Select  
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)   
  From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
    Where y.Level_No=@Level_No-1  
and  x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
 From  #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No 
 Where b.Level_No=@Level_No-1  
) a
  End


Select xPath,Cost1,Cost2 From #Tmp_ExpandBom




dbo.IntToStr()  函数提供一下?

#5


引用 4 楼 backbone601 的回复:
Quote: 引用 2 楼 appetizing_fish1 的回复:



if object_id('tempdb..#Tmp_iBomDetTbl') is not null
drop table #Tmp_iBomDetTbl

CREATE TABLE #Tmp_iBomDetTbl (
Detail_ID int identity(1,1),
SubItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
ParentItem_No varchar(50) COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
cost2 int)

Insert into #Tmp_iBomDetTbl Values ('A','',100,100)
Insert into #Tmp_iBomDetTbl Values ('B1','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('B2','A',100,100)
Insert into #Tmp_iBomDetTbl Values ('C1','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C2','B1',100,100)
Insert into #Tmp_iBomDetTbl Values ('C3','B2',100,100)
Insert into #Tmp_iBomDetTbl Values ('C4','B2',100,100)

--Select * From #Tmp_iBomDetTbl
if object_id('tempdb..#Tmp_ExpandBom') is not null
drop table #Tmp_ExpandBom

Create   TABLE  #Tmp_ExpandBom
(
Level_No  int,
Level_Desc varchar(100) COLLATE Chinese_*_Stroke_CI_AS NULL,
Item_No   varchar(50)COLLATE Chinese_*_Stroke_CI_AS NULL,
Cost1 int,
Cost2 int,
xPath   varchar(1000) COLLATE Chinese_*_Stroke_CI_AS NULL
)   


DECLARE @Level_No int   
SET @Level_No=1   

--先加入最上層的一條記錄,
 INSERT   #Tmp_ExpandBom   (Level_No,Level_Desc,Item_No,Cost1,Cost2,xPath)
 Select  Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,Cost1,Cost2,ParentItem_No
 From   #Tmp_iBomDetTbl   a   
 Where  Not Exists(select SubItem_No from #Tmp_iBomDetTbl where SubItem_No=a.ParentItem_No )
  
    
  WHILE   @@rowcount>0    --根據最上層的一條記錄依次找出下一級數據
BEGIN   
  SET   @Level_No=@Level_No+1   
  INSERT   into #Tmp_ExpandBom  (Level_No,Level_Desc,Item_No,Cost1,cost2,xPath)

Select Level_No,Level_Desc,SubItem_No,Cost1,cost2,xPath
From
(
 Select  
@Level_No as 'Level_No',
b.Level_Desc+dbo.IntToStr((1+(Select count(ParentItem_No)   
  From #Tmp_iBomDetTbl x join #Tmp_ExpandBom y on x.ParentItem_No=y.Item_No
    Where y.Level_No=@Level_No-1  
and  x.Detail_id<a.Detail_id
and x.ParentItem_No=a.ParentItem_No)),3) as 'Level_Desc',
a.SubItem_No,
a.Cost1,a.Cost2,
b.xPath+'>'+a.SubItem_No as 'xPath'
 From  #Tmp_iBomDetTbl a join #Tmp_ExpandBom b on a.ParentItem_No=b.Item_No 
 Where b.Level_No=@Level_No-1  
) a
  End


Select xPath,Cost1,Cost2 From #Tmp_ExpandBom




dbo.IntToStr()  函数提供一下?


Create FUNCTION [dbo].[IntToStr]
(@IntValue int,@Lenth int)
RETURNS Nvarchar(20)
AS
BEGIN
Return REPLICATE('0',@Lenth-len(ltrim(rtrim(str( @IntValue))))) + ltrim(rtrim(str(@IntValue)))
END

#6


@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

#7


借用2楼的数据。

WITH CTE1
AS
(SELECT *,ParentItem_No AS NO1,SubItem_No AS NO2,CAST('' AS VARCHAR(50)) AS NO3 ,1 AS LEVEL 
 FROM #A A
 WHERE EXISTS (SELECT 1 FROM #A WHERE A.SubItem_No=ParentItem_No)
       AND EXISTS (SELECT 1 FROM #A WHERE A.ParentItem_No=SubItem_No)
 UNION ALL
 SELECT B.*,B.SubItem_No,CAST('' AS VARCHAR(50)),CAST('' AS VARCHAR(50)),C.LEVEL+1
 FROM #A B 
 JOIN CTE1 C ON B.SubItem_No=C.ParentItem_No
 WHERE C.LEVEL>=1
 UNION ALL
 SELECT D.*,E.NO1,E.NO2,CAST(D.SubItem_No AS VARCHAR(50)),E.LEVEL-1
 FROM #A D 
 JOIN CTE1 E ON D.ParentItem_No=E.SubItem_No
 WHERE E.LEVEL<=1)
 
 SELECT NO1,NO2,NO3,Cost1,cost2,LEVEL FROM CTE1

#8


@rink_1 

提示 在递归查询 "CTE1" 的列 "NO3" 中,定位点类型和递归部分的类型不匹配。

另外:No1 No2 No3 也不是固定的,如果数据有四层的话,需要自动产生四个字段。

#9


引用 6 楼 backbone601 的回复:
@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

接上这些代码


DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(50)
DECLARE @Item_No varchar(50)

DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1

While @minLevel_No is not null
       Begin
 
              Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
              
              SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
              
              
              SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
      exec (@sql)
      set @sql=''
      
      SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where Level_No>'+cast(@minLevel_No-1 as varchar)

               exec (@sql)
               
               Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
       
       End
       
Select * From   #Tmp_ExpandBom     

#10


引用 9 楼 appetizing_fish1 的回复:
Quote: 引用 6 楼 backbone601 的回复:

@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

接上这些代码


DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(50)
DECLARE @Item_No varchar(50)

DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1

While @minLevel_No is not null
       Begin
 
              Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
              
              SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
              
              
              SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
      exec (@sql)
      set @sql=''
      
      SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where Level_No>'+cast(@minLevel_No-1 as varchar)

               exec (@sql)
               
               Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
       
       End
       
Select * From   #Tmp_ExpandBom     





引用 9 楼 appetizing_fish1 的回复:
Quote: 引用 6 楼 backbone601 的回复:

@appetizing_fish1 

谢谢您的答复!

代码运行结果是:

xPath Cost1 Cost2
100 100
>A 100 100
>A>B1 100 100
>A>B2 100 100
>A>B1>C1 100 100
>A>B1>C2 100 100
>A>B2>C3 100 100
>A>B2>C4 100 100

这与目标有些差异,需要的是自动产生三个字段:物料1,物料2,物料3

接上这些代码


DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50)
DECLARE @Level_Desc varchar(50)
DECLARE @Item_No varchar(50)

DECLARE @minLevel_No int
Select @minLevel_No=Min(Level_No) From #Tmp_ExpandBom Where Level_No>1

While @minLevel_No is not null
       Begin
 
              Select @Item_No=Item_No From #Tmp_ExpandBom Where Level_No=@minLevel_No
              
              SET @Title='Item'+CAST(@minLevel_No-1 as varchar)
              
              
              SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
      exec (@sql)
      set @sql=''
      
      SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where Level_No>'+cast(@minLevel_No-1 as varchar)

               exec (@sql)
               
               Select @minLevel_No=MIN(Level_No) From #Tmp_ExpandBom Where Level_No>@minLevel_No
       
       End
       
Select * From   #Tmp_ExpandBom     



上一个有误,用这个吧



DECLARE @sql nvarchar(2000)
SET @sql=''
DECLARE @Title nvarchar(50) 
DECLARE @Level_Desc varchar(100)
DECLARE @lenLevel_Desc int
DECLARE @Item_No varchar(50)
DECLARE @PrevLevel_No int
SET @PrevLevel_No=0
DECLARE @CurrLevel_No int
DECLARE @minDetail_ID int
Select @minDetail_ID=Min(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>1
While @minDetail_ID is not null
       Begin
               Select @Level_Desc=Level_Desc,@lenLevel_Desc=len(level_desc),@Item_No=Item_No,@CurrLevel_No=Level_No 
               From #Tmp_ExpandBom 
               Where Detail_ID=@minDetail_ID
               
               IF @CurrLevel_No>1 And @CurrLevel_No<>@PrevLevel_No
   Begin
  SET @PrevLevel_No=@CurrLevel_No
  SET @Title='Item'+CAST(@CurrLevel_No-1 as varchar)
  SET  @sql=' Alter Table #Tmp_ExpandBom Add ' + @Title+' nvarchar(100) '
  exec (@sql)                   
   End
   
SET @sql=''
   
     SET  @sql=@sql+'  Update #Tmp_ExpandBom ' 
+'  Set '+@Title+' ='''+@Item_No+''''
    +'  Where substring(Level_Desc,1,'+cast(@lenLevel_Desc as varchar)+')='''+@Level_Desc+''''

               exec (@sql)    
   

  Select @minDetail_ID=MIN(Detail_ID) From #Tmp_ExpandBom Where Detail_ID>@minDetail_ID
              
       End

Select * From #Tmp_ExpandBom        
  

#11


@appetizing_fish1

可以了,谢谢!