还是BOM表穿透半成品查询子项的问题
是SQL2000,不是SQL2005，所以不能使用WITH AS 如何实现？
原始表
FUIDFUMS ZIIDZIMS QTYLVL
00-001cp 01-001bcp1 10
01-001bcp1 05-001ycl1 0.51
01-001bcp1 01-002bcp2 21
01-002bcp2 06-001ycl2 12
01-002bcp2 06-002ycl3 32
结果表
FUIDFUMX ZIIDZIMS QTY
00-001cp 05-001ycl1 0.5
00-001cp 06-001ycl2 2
00-001cp 06-002ycl3 6
如何穿透半成品直接取出原材料的用量
生成表语句
IF OBJECT_ID ( '[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([fuID] [nvarchar] ( 10), [fums] [nvarchar]( 10), [ziID][nvarchar] (10 ), [zims] [nvarchar]( 10 ),[qty] [decimal], [lvl] [int])
INSERT INTO [tb]
SELECT '00-001' , 'cp ' ,'01-001' , 'bcp1 ' ,'1' , '0' UNION ALL
SELECT '01-001' , 'bcp1 ' ,'05-001' , 'ycl1 ' ,'0.5' , '1' UNION ALL
SELECT '01-001' , 'bcp1 ' ,'01-002' , 'bcp2 ' ,'2' , '1' UNION ALL
SELECT '01-002' , 'bcp2 ' ,'06-001' , 'ycl2 ' ,'1' , '2' UNION ALL
SELECT '01-002' , 'bcp2 ' ,'06-002' , 'ycl3 ' ,'3' , '2'
[解决办法]
试试这个：
USE tempdb
GO
IF OBJECT_ID ( '[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]
(
[fuID] [nvarchar](10),
[fums] [nvarchar](10),
[ziID] [nvarchar](10),
[zims] [nvarchar](10),
[qty] [decimal](10,2),
[lvl] [int]
)
INSERT INTO [tb]
SELECT '00-001' , 'cp ' ,'01-001' , 'bcp1 ' , 1 , 0 UNION ALL
SELECT '01-001' , 'bcp1 ' ,'05-001' , 'ycl1 ' ,0.5 , 1 UNION ALL
SELECT '01-001' , 'bcp1 ' ,'01-002' , 'bcp2 ' , 2 , 1 UNION ALL
SELECT '01-002' , 'bcp2 ' ,'06-001' , 'ycl2 ' , 1 , 2 UNION ALL
SELECT '01-002' , 'bcp2 ' ,'06-002' , 'ycl3 ' , 3 , 2
GO
SELECT * FROM tb
GO
SET NOCOUNT ON
DECLARE @tb_tmp_all TABLE
(
fuIDnvarchar(10),
fumsnvarchar(10),
ziIDnvarchar(10),
zimsnvarchar(10),
qtydecimal(10,2)
)
DECLARE @tb_tmp TABLE
(
fuIDnvarchar(10),
fumsnvarchar(10),
tmp_fuID nvarchar(10),
ziIDnvarchar(10),
zimsnvarchar(10),
qtydecimal(10,2)
)
INSERT INTO @tb_tmp SELECT fuID,fums,ziID,ziID,zims,qty FROM tb WHERE lvl = 0
INSERT INTO @tb_tmp_all SELECT fuID,fums,ziID,zims,qty FROM @tb_tmp
DECLARE @nLevel INT = 1
WHILE 1=1
BEGIN
DELETE FROM @tb_tmp
INSERT INTO @tb_tmp
SELECT
A.fuID,
A.fums,
B.fuID,
B.ziID,
B.zims,
A.qty * B.qty
FROM
@tb_tmp_all AS A
INNER JOIN tb AS B ON B.fuID = A.ziID
WHERE
B.lvl = @nLevel
IF @@ROWCOUNT = 0
BREAK
DELETE FROM @tb_tmp_all WHERE ziID IN(SELECT tmp_fuID FROM @tb_tmp)
INSERT INTO @tb_tmp_all SELECT fuID,fums,ziID,zims,qty FROM @tb_tmp
SET @nLevel = @nLevel + 1
END
SET NOCOUNT OFF
SELECT * FROM @tb_tmp_all
