22,209
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(FID VARCHAR(20),
CID VARCHAR(20),
QTY INT,
PRICE INT)
INSERT INTO #T
select N'' AS 'Father',N'A1' AS 'No2',1 AS 'Qty',0 AS 'Price' union all
select N'A1',N'A01',2, 6 union all
select N'A1',N'A02',2, 5 union all
select N'A1',N'A03',1, 3 union all
select N'A02',N'A0201',1, 5 union all
select N'A02',N'A0202',1, 5 union all
select N'A02',N'A0203',1, 3
WITH CTE
AS
(SELECT *,PRICE AS SUB_PRICE
FROM #T A
WHERE NOT EXISTS (SELECT 1 FROM #T WHERE FID=A.CID)
UNION ALL
SELECT A.*,SUB_PRICE*A.QTY
FROM #T A
JOIN CTE B ON A.CID=B.FID)
SELECT FID,CID,QTY,SUM(SUB_PRICE) FROM CTE
GROUP BY FID,CID,QTY
/*父件、子件、数量、金额*/
select N'A1' AS 'Father',N'' AS 'No2',1 AS 'Qty',0 AS 'Price' union all
select N'A1',N'A01',2, 6 union all
select N'A1',N'A02',2, 5 union all
select N'A1',N'A03',1, 3 union all
select N'A02',N'A0201',1, 5 union all
select N'A02',N'A0202',1, 5 union all
select N'A02',N'A0203',1, 3
结果
select N'A1' AS 'Father',N'' AS 'No2',1 AS 'Qty',35 AS 'Price' union all
select N'A1',N'A01',2, 6 union all
select N'A1',N'A02',2, 26 union all
select N'A1',N'A03',1, 3 union all
select N'A02',N'A0201',1, 5 union all
select N'A02',N'A0202',1, 5 union all
select N'A02',N'A0203',1, 3