|
谢谢老师提醒,其实,我本意为两张表,一个是BOM的主件表,另一个为主件的子件表,我想更好的体现BOM的分层与计算,看到老师的SQL处理方法,就多想了一些。现在我的解决办法跟老师的差不多,速度很快。我处理的代码如下:
WITH CTE AS (SELECT 0 AS fid, mid, 0 AS Fmid, cast(0 AS decimal(18, 6)) AS 用量, cast('' AS nvarchar(255)) AS 备注, cast('' AS nvarchar(max)) AS TE, ROW_NUMBER()
OVER (ORDER BY getdate()) AS OrderID, 0 AS Levle, mid AS 产品
FROM tbl_bom
UNION ALL
SELECT tbl_bomsub.fid, tbl_bomsub.mid, tbl_bomsub.Fmid, tbl_bomsub.用量, tbl_bomsub.备注, cast(replicate('-', len(CTE.TE)) + '-' AS nvarchar(MAX)) AS TE,
CTE.OrderID * 100 + ROW_NUMBER() OVER (ORDER BY GETDATE()) AS OrderID, CTE.Levle + 1 AS Levle, CTE.产品
FROM tbl_bomsub INNER JOIN
CTE ON tbl_bomsub.Fmid = CTE.mid)
SELECT *
FROM CTE
|
|