求 sql？
IF EXISTS ( SELECT 1
FROM tempdb.sys.sysobjects
WHERE name LIKE '#TMP_ITM%'
AND xtype = 'U' )
DROP TABLE #TMP_ITM
GO
CREATE TABLE #TMP_ITM
(
MOTHER_ITEM_CD NVARCHAR(20) NOT NULL
,CHIDL_ITM_CD NVARCHAR(20) NOT NULL
,QTY DECIMAL(17, 4) NULL
)
INSERT INTO #TMP_ITM(MOTHER_ITEM_CD,CHIDL_ITM_CD,QTY )VALUES ('000001','00002',10.0000 );
INSERT INTO #TMP_ITM(MOTHER_ITEM_CD,CHIDL_ITM_CD,QTY )VALUES ('000001','1012-10',20.0000 );
INSERT INTO #TMP_ITM(MOTHER_ITEM_CD,CHIDL_ITM_CD,QTY )VALUES ('1012-10','002',50.0000 );
INSERT INTO #TMP_ITM(MOTHER_ITEM_CD,CHIDL_ITM_CD,QTY )VALUES ('1012-10','1055-10',70.0000 );
INSERT INTO #TMP_ITM(MOTHER_ITEM_CD,CHIDL_ITM_CD,QTY )VALUES ('1055-10','LJKJL',100.0000 );
SELECT * FROM #TMP_ITM
/*
母ID子ID数量
MOTHER_ITEM_CDCHIDL_ITM_CDQTY
0000010000210.0000
0000011012-1020.0000
1012-1000250.0000
1012-101055-1070.0000
1055-10LJKJL100.0000
*/
/*
说明：
1。根据 MOTHER_ITEM_CD 插入 一条。。如，下面的 SEQ 等于 0，6，10 ITEM_CD 和 CHIDL_ITM_CD 都是 MOTHER_ITEM_CD。LV = 0 ，QTY 是 1.0000
2。然后 以 CHIDL_ITM_CD 为键 搜索 #TMP_ITM 表的 MOTHER_ITEM_CD，如 以 CHIDL_ITM_CD = 00002 来 搜索 MOTHER_ITEM_CD为 00002，
不存在==〉 插入 像 seq 等于 1 LV 是= 1 ，QTY 是 10.0000
如果存在==〉如 CHIDL_ITM_CD = 1012-10，CHIDL_ITM_CD = 1012-10 来 搜索 MOTHER_ITEM_CD为 1012-10 的 所有数据 依次 循环，
这里 有两条
1012-1000250.0000
1012-101055-1070.0000（1055-10是1012-10的子ID，所以层次是 2， 1055-10 因为 存在 所以又搜索。。）
*/
/*
想要的结果
顺序层次数量
SEQITEM_CDCHIDL_ITM_CDLVQTY
000000100000101.0000
100000100002110.0000
20000011012-10120.0000
3000001002250.0000
40000011055-10270.0000
5000001LJKJL3100.0000
61012-101012-1001.0000
71012-10002150.0000
81012-101055-10170.0000
91012-10LJKJL2100.0000
101055-101055-1001.0000
111055-10LJKJL1100.0000
*/
DECLARE @tb TABLE
(
MOTHER_ITEM_CD VARCHAR(20) NOT NULL ,
CHIDL_ITM_CD VARCHAR(20) NOT NULL ,
qty DECIMAL(17, 4) NOT NULL ,
lvl INT NOT NULL
)
DECLARE @root VARCHAR(20)
DECLARE CP_cursor CURSOR
FOR
SELECT DISTINCT
MOTHER_ITEM_CD
FROM dbo.TB
OPEN CP_cursor
FETCH NEXT FROM CP_cursor
INTO @root ;
WHILE @@FETCH_STATUS = 0
BEGIN
WITH cte
AS ( SELECT MOTHER_ITEM_CD ,
CHIDL_ITM_CD ,
qty ,
1 AS lvl
FROM TB
WHERE MOTHER_ITEM_CD = @root
UNION ALL
SELECT cte.MOTHER_ITEM_CD ,
A.CHIDL_ITM_CD ,
CAST(cte.qty * A.qty AS DECIMAL(17, 4)) ,
cte.lvl + 1
FROM cte
INNER JOIN TB A ON A.MOTHER_ITEM_CD = cte.CHIDL_ITM_CD
)
INSERT INTO @tb
( MOTHER_ITEM_CD ,
CHIDL_ITM_CD ,
qty ,
lvl
)
SELECT *
FROM cte
UNION ALL
SELECT MOTHER_ITEM_CD ,
MOTHER_ITEM_CD ,
1 ,
0
FROM TB
WHERE MOTHER_ITEM_CD = @root
FETCH NEXT FROM CP_cursor
INTO @root ;
END
CLOSE CP_cursor ;
DEALLOCATE CP_cursor ;
SELECT ROW_NUMBER() OVER ( ORDER BY GETDATE() ) AS SEQ ,
*
FROM ( SELECT DISTINCT TOP 100 PERCENT
*
FROM @tb
ORDER BY MOTHER_ITEM_CD ,
lvl ASC
) A
/*
SEQ MOTHER_ITEM_CD CHIDL_ITM_CD qty lvl
-------------------- -------------------- -------------------- --------------------------------------- -----------
1 000001 000001 1.0000 0
2 000001 00002 10.0000 1
3 000001 002 1000.0000 2
4 000001 1012-10 20.0000 1
5 000001 1055-10 1400.0000 2
6 000001 LJKJL 140000.0000 3
7 1012-10 002 50.0000 1
8 1012-10 1012-10 1.0000 0
9 1012-10 1055-10 70.0000 1
10 1012-10 LJKJL 7000.0000 2
11 1055-10 1055-10 1.0000 0
12 1055-10 LJKJL 100.0000 1
(12 行受影响)
*/
