bom清单阶次问题
bom表(产品,零件，用量)
数据：
--------------------------------------------
产品 零件 用量
--------------------------------------------
a a1 1
a1 a11 1
a11 a111 1
a a2 1
a2 a22 1
a22 a222 1
b b1 1
b1 b11 1
b b2 1
b2 b22 1
b22 b222 1
请问如何列出产品所用零件的零件bom阶次清单
例：
--------------------------------------------
阶次 产品 零件 用量
--------------------------------------------
0 a
.1 a1 1
..2 a11 1
...3 a111 1
.1 a2 1
..2 a22 1
...3 a222 1
谢谢！
[最优解释]
USE test
GO
-->生成表bom
--if object_id('bom') is not null
--drop table bom
--Go
--Create table bom([产品] nvarchar(3),[零件] nvarchar(4),[用量] smallint)
--Insert into bom
--Select N'a',N'a1',1
--Union all Select N'a1',N'a11',1
--Union all Select N'a11',N'a111',1
--Union all Select N'a',N'a2',1
--Union all Select N'a2',N'a22',1
--Union all Select N'a22',N'a222',1
--Union all Select N'b',N'b1',1
--Union all Select N'b1',N'b11',1
--Union all Select N'b',N'b2',1
--Union all Select N'b2',N'b22',1
--Union all Select N'b22',N'b222',1
;WITH Result AS (
SELECT
1 AS LEVEL
,ROW_NUMBER()OVER(PARTITION BY 产品 ORDER BY 零件) AS Row
,*
FROM bom AS a
WHERE NOT EXISTS(SELECT 1 FROM bom AS x WHERE x.零件=a.产品)
UNION ALL
SELECT
b.Level+1
,b.Row
,b.产品
,a.零件
,a.用量
FROM bom AS a
INNER JOIN Result AS b ON a.产品=b.零件
)
SELECT
阶次
,CASE WHEN Level>0 THEN '' ELSE 产品 END AS 产品
,零件
,用量
FROM (
SELECTDISTINCT
'0'AS 阶次
,产品
,''AS 零件
,用量
,0AS LEVEL
,1 AS Row
FROM bom AS a
WHERE NOT EXISTS(SELECT 1 FROM bom AS x WHERE x.零件=a.产品)
UNION ALL
SELECT
REPLICATE('.',Level)+LTRIM(Level)
,产品
,零件
,用量
,Level
,Row
FROM Result
) AS t
ORDER BY t.产品,t.Row,t.Level
/*
阶次 产品 零件 用量
----- ---- ---- ------
0 a 1
.1 a1 1
..2 a11 1
...3 a111 1
.1 a2 1
..2 a22 1
...3 a222 1
0 b 1
.1 b1 1
..2 b11 1
.1 b2 1
..2 b22 1
...3 b222 1
*/
select 'a' AS cp,'a1' AS lj,'1' AS yl INTO #1 union all
select 'a1','a11','1' union all
select 'a11','a111','1' union all
select 'a','a2','1' union all
select 'a2','a22','1' union all
select 'a22','a222','1' union all
select 'b','b1','1' union all
select 'b1','b11','1' union all
select 'b','b2','1' union all
select 'b2','b22','1' union all
select 'b22','b222','1'
;WITH t AS (
SELECT 1 AS js,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS id,t1.cp,t1.lj,t1.yl
FROM #1 AS t1
WHERE t1.cp = 'a'
UNION ALL
SELECT t.js + 1,t.id * 10 + ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS id,t2.cp,t2.lj,t2.yl
FROM #1 AS t2,t
WHERE t2.cp = t.lj
)
SELECT t.js,t.lj,t.yl FROM t ORDER BY CAST(t.id AS VARCHAR)
DROP TABLE #1
js lj yl
----------- ---- ----
1 a1 1
2 a11 1
3 a111 1
1 a2 1
2 a22 1
3 a222 1
(6 行受影响)
..2 a22 1
...3 a222 1
[其他解释]
再加20分，希望有更好的解决方案。
[其他解释]
declare @bom table([产品] nvarchar(3),[零件] nvarchar(4),[用量] smallint)
Insert into @bom
Select N'a',N'a1',1
Union all Select N'a1',N'a11',1
Union all Select N'a11',N'a111',1
Union all Select N'a',N'a2',1
Union all Select N'a2',N'a22',1
Union all Select N'a22',N'a222',1
Union all Select N'b',N'b1',1
Union all Select N'b1',N'b11',1
Union all Select N'b',N'b2',1
Union all Select N'b2',N'b22',1
Union all Select N'b22',N'b222',1;
WITH Result AS
(
SELECT 1 AS LEVEL, ROW_NUMBER()OVER(PARTITION BY 产品 ORDER BY 零件) AS Row, *
FROM @bom AS a
WHERE NOT EXISTS(SELECT 1 FROM @bom AS x WHERE x.零件=a.产品)
UNION ALL
SELECT b.Level+1, b.Row, b.产品, a.零件, a.用量
FROM @bom AS a INNER JOIN Result AS b ON a.产品=b.零件
)
SELECT 阶次, CASE WHEN Level>0 THEN '' ELSE 产品 END AS 产品, 零件, 用量
FROM (
SELECT DISTINCT '0' AS 阶次, 产品, '' AS 零件, 用量, 0 AS LEVEL, 1 AS Row
FROM @bom AS a
WHERE NOT EXISTS(SELECT 1 FROM @bom AS x WHERE x.零件=a.产品)
UNION ALL
SELECT REPLICATE('.',Level)+LTRIM(Level), 产品, 零件, 用量, Level, Row
FROM Result
) AS t
ORDER BY t.产品,t.Row,t.Level
