求BOM多阶展阶显示取替代料SQL语句(100)

BOM表如下: BOM1主件 BOM2子件 BOM3数量 A B 1 A C2 1 A D 1 A M 1 D E 1 D F 1 E G 1 E H 1 E B 1 E_BOM替代料表如下： E_BOM1主件 E_BOM2原子件 E_BOM3新子件 E_BOM4数量*(所有主件) B B2 1A C2 C 2如何查询A物料BOM得到以下格式：阶次 子件 数量 替代料1 B 1 * B2 1 替代1 C2 1 * C 2 替代1 D 1 2 E 1 3 G 1 3 H 1 3 B 1 * B2 1 替代2 F 1 1 M 1

阶次通过sql好像没法实现。Select 主件,子件,数量, 替代料from (select 主件,子件,数量, '' as 替代料, 子件 as 原子件, 0 as OrderID from BOM union all select a.主件,b.新子件 as 子件, a.数量*b.数量 as 数量, '替代' as 替代料, a.子件 as 原子件, 1 as OrderID from BOM a, BOM替代料表 b where (a.主件=b.主件 or b.主件='*') and a.子件=b.原子件 ) as xorder by 主件,原子件,OrderID

写了自定义函数来实现，多阶物料是没有问题，就是替代料件做不出来CREATE function BOM1(@parent VARCHAR(20)) --BOM多阶查询returns @temptable table(level tinyint,BOM1 Nvarchar(20),BOM2 Nvarchar(20), BOM3 numeric(9,3), MD00Z Nvarchar(1000)collate Latin1_General_BIN) asbegin declare @level int set @level=1 insert into @temptable select @level,BOM1,BOM2,BOM3,BOM1+BOM2 from BOM where BOM1=@parent collate Latin1_General_BIN while @@rowcount>0 begin set @level=@level+1 insert @temptable select @level,BOM.BOM1,BOM.BOM2,BOM.BOM3,B.BOM1+B.BOM2 from BOM,@temptable B where BOM.BOM1=B.BOM2 collate Latin1_General_BIN and B.level=@level-1 endreturnendSELECT replicate('.',level*1)+ltrim(str(level)),*FROM BOM1('A')order by MD00Z

Select 阶次,子件,数量, 替代料from (select 你的函数() as 阶次, a.主件,a.子件,a.数量, (case when b.主件 is null then null else '*' end ) as 替代料, a.子件 as 原子件, 0 as OrderID from BOM a left join BOM替代料表 b on (a.主件=b.主件 or b.主件='*') and a.子件=b.原子件 union all select 你的函数() as 阶次, a.主件,b.新子件 as 子件, a.数量*b.数量 as 数量, '替代' as 替代料, a.子件 as 原子件, 1 as OrderID from BOM a inner join BOM替代料表 b (a.主件=b.主件 or b.主件='*') and a.子件=b.原子件 ) as xorder by 阶次, 主件,原子件,OrderID

测试了好久，调用后结果还是不对SELECT 阶次,主件,子件, 数量, 替代料FROM ( select replicate('.',LEVEL*1)+ltrim(str(LEVEL)) as 阶次, a.主件, a.子件, a.数量, (CASE WHEN b.主件 IS NULL THEN NULL ELSE '*' END) AS 替代料, a.子件 AS 原子件, 0 AS OrderID FROM BOM a LEFT JOIN BOM替代料表 b ON (a.主件 = b.主件 OR b.主件 = '*') AND a.子件 = b.原子件,BOM1('A') UNION ALL SELECT replicate('.',LEVEL*1)+ltrim(str(LEVEL)) as 阶次, a.主件, b.新子件 AS 子件, a.数量*b.数量 AS 数量, '替代' AS 替代料, a.子件 AS 原子件, 1 AS OrderID FROM BOM a INNER JOIN BOM替代料表 b ON (a.主件 = b.主件 OR b.主件 = '*') AND a.子件 = b.原子件,BOM1('A')) AS xORDER BY 阶次,主件, 原子件, OrderID

BOM1('A') 返回的是一个数据集吧，怎么没见到你把它和其它表管理？

CREATE function BOM1(@parent VARCHAR(20))returns @temptable table(LEVEL tinyint,主件 Nvarchar(20),子件 Nvarchar(20), 数量 numeric(9,3),替代料 Nvarchar(20),原子件 Nvarchar(20), OrderID numeric(9,3) ) asbegin declare @LEVEL int set @LEVEL=1 insert into @temptable select @LEVEL,a.主件,a.子件,a.数量,(CASE WHEN b.主件 IS NULL THEN NULL ELSE '*' END) AS 替代料, a.子件 AS 原子件, 0 AS OrderID FROM BOM a LEFT JOIN BOM替代料表 b ON (a.主件 = b.主件 OR b.主件 = '*') AND a.子件 = b.原子件 where a.主件=@parentunion all SELECT @LEVEL, a.主件, b.新子件 AS 子件, b.数量 AS 数量, '替代' AS 替代料,a.子件 AS 原子件, 1 AS OrderID FROM BOM a INNER JOIN BOM替代料表 b ON (a.主件 = b.主件 OR b.主件 = '*') AND a.子件 = b.原子件 --where (b.主件=@parent ) while @@rowcount>0 begin set @LEVEL=@LEVEL+1 insert @temptable select @LEVEL,a.主件,a.子件,a.数量,(CASE WHEN b.主件 IS NULL THEN NULL ELSE '*' END) AS 替代料, a.子件 AS 原子件, 0 AS OrderID FROM BOM a LEFT JOIN BOM替代料表 b ON (a.主件 = b.主件 OR b.主件 = '*') AND a.子件 = b.原子件, @temptable BB where a.主件=BB.子件 and BB.LEVEL=@LEVEL-1 union all SELECT @LEVEL, a.主件, b.新子件 AS 子件, b.数量 AS 数量, '替代' AS 替代料,a.子件 AS 原子件, 1 AS OrderID FROM BOM a INNER JOIN BOM替代料表 b ON (a.主件 = b.主件 OR b.主件 = '*') AND a.子件 = b.原子件 , @temptable BB where BB.子件=a.主件 AND BB.LEVEL=@LEVEL-1 endreturnendSELECT replicate('.',LEVEL*1)+ltrim(str(LEVEL)),*FROM BOM1('A')order by LEVEL,主件,原子件,OrderID --结果是出来了，但排序后的结果不对

顶，

既然结果已经出来了，只是排序后的结果不符合你的要求，你参考数据，编写合适的order by 语句就可以了啊。或者，你把现在的输出结果贴出来，让大家帮忙。

谢谢了，根据znxia提示的思路，做出来了