有母件与子件两个表，如何层级展示

[flyfly2008]

就是一个母件表和一个子件表，子件表中又包涵了母件，如果查询母件时，可以显示子件层级结构

[flyfly2008]

---测试数据 if not object_id(N'Tempdb..#Tmp_fa') is null drop table #Tmp_fa Go Create table #Tmp_fa(bomid varchar(10),) Insert #Tmp_fa select 'A' union all select 'B' union all select 'C' union all select 'D' union all select 'F' union all select 'E' union all select 'G' union all select 'M' union all select 'N' go if not object_id(N'Tempdb..#Tmp_so') is null drop table #Tmp_so Create table #Tmp_so(bomid varchar(10),son varchar(10)) Insert #Tmp_so select 'A','B' union all select 'A','C' union all select 'A','D' union all select 'B','E' union all select 'B','F' union all select 'C','G' union all select 'C','G' union all select 'G','I' union all select 'E','O'

[顺势而为1]

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

CREATE TABLE #Tmp_iBomDetTbl (
					Detail_ID int identity(1,1),
					ParentItem_No varchar(50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
					SubItem_No varchar(50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL)
								
Insert into #Tmp_iBomDetTbl Values ('A','B')
Insert into #Tmp_iBomDetTbl Values ('A','C')
Insert into #Tmp_iBomDetTbl Values ('A','D')
Insert into #Tmp_iBomDetTbl Values ('B','E')
Insert into #Tmp_iBomDetTbl Values ('B','F')
Insert into #Tmp_iBomDetTbl Values ('C','G')
Insert into #Tmp_iBomDetTbl Values ('C','H')
Insert into #Tmp_iBomDetTbl Values ('G','I')
Insert into #Tmp_iBomDetTbl Values ('G','O')


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

Create   TABLE  #Tmp_ExpandBom
		(
			Detail_ID int identity(1,1),
			Level_No  int,
			Level_Desc varchar(100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
			Item_No   varchar(50)COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
			xPath   varchar(1000) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
		)   
		

DECLARE @Level_No int   
SET @Level_No=1   

	--先加入最上層的一條記錄,
 INSERT   #Tmp_ExpandBom   (Level_No,Level_Desc,Item_No,xPath)
 Select  Distinct @Level_No,dbo.IntToStr(@Level_No,3),a.ParentItem_No,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,xPath)

						Select Level_No,Level_Desc,SubItem_No,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,
											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 * From 	#Tmp_ExpandBom	  

[二月十六]

  ;WITH cte AS(
  SELECT    * ,
            1 AS lvl 
  FROM      #Tmp_so
  WHERE     bomid = 'A'
  UNION ALL
  SELECT    a.* ,
            b.lvl + 1 
  FROM      #Tmp_so a
            JOIN cte b ON b.son = a.bomid
  )
  SELECT lvl,son FROM cte  

[卖水果的net]

create table test(bomId varchar(10), son varchar(10))
go
insert into test values
('A','B'),('A','C'),('A','D'),
('B','E'),('B','F'),
('C','G'),('C','H'),
('G','I'),
('E','O')
go
with m as (
	select 1 l, row_number() over(order by bomId) rn, t.bomId, t.son 
	from test t 
	where bomid = 'A'
	union all
	select m.l + 1, m.rn  * 10 + row_number() over(order by m.l) , t.bomId, t.son
	from test t, m 
	where t.bomId = m.son
)
select replicate('+',l) + ltrim(l) Level,  bomId , son 
from m 
order by cast(rn as varchar(30))
go
drop table test 
go


(9 行受影响)
Level                                       bomId      son
-------------------------------------------- ---------- ----------
+1                                          A          B
++2                                         B          E
+++3                                        E          O
++2                                         B          F
+1                                          A          C
++2                                         C          G
+++3                                        G          I
++2                                         C          H
+1                                          A          D

(9 行受影响)

[flyfly2008]

@appetizing_fish1 IntToStr 这个函数？