关于网友提出的“ 有母件与子件两个表，如何层级展示”问题疑问，本网通过在网上对“ 有母件与子件两个表，如何层级展示”有关的相关答案进行了整理，供用户进行参考，详细问题解答如下：
问题： 有母件与子件两个表，如何层级展示
描述:SQL
就是一个母件表和一个子件表，子件表中又包涵了母件，如果查询母件时，可以显示子件层级结构
解决方案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<>
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
解决方案2:
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 行受影响)
解决方案3: ;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
以上介绍了“ 有母件与子件两个表，如何层级展示”的问题解答，希望对有需要的网友有所帮助。
本文网址链接：http://www.codes51.com/itwd/4378138.html
