27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT a.[成套编码] as 一层,a.[套内编码] as 二层,b.[套内编码] as 三层
FROM #tab a
left JOIN #tab b ON a.[套内编码] =b.[成套编码]
WHERE a.[成套编码]='测试产品'
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([成套编码] nvarchar(24),[套内编码] nvarchar(26),[继续拆分] varchar(20))
Insert #tab
select N'测试产品',N'测试分包1','是' union all
select N'测试产品',N'测试零件1','否' union all
select N'测试产品',N'测试零件2','否' union all
select N'测试产品',N'测试部件1','是' union all
select N'测试分包1',N'分包拆零件1','否' union all
select N'测试分包1',N'分包拆零件2','否' union all
select N'测试分包2',N'分包拆零件3','否' union all
select N'测试分包2',N'分包拆零件4','否' union all
select N'测试部件1',N'部件拆零件1','否' union all
select N'测试产品',N'测试分包2','是'
GO
--测试数据结束
;WITH cte AS (
Select *, [成套编码] as title from #tab WHERE [成套编码]='测试产品'
UNION ALL
SELECT a.*,b.title FROM #tab a
INNER JOIN cte b ON a.[成套编码] =b.[套内编码]
)
SELECT title AS [成套编码],[套内编码]
FROM cte
WHERE [继续拆分]='否'