27,580
社区成员
发帖
与我相关
我的任务
分享
SELECT * INTO #t FROM (
select 级别= 1 ,A='A1',B='B1',用量= 1 ,B真实用量=null UNION ALL
select 级别= 2 ,A='B1',B='C1',用量= 2 ,B真实用量=null UNION ALL
select 级别= 3 ,A='C1',B='D1',用量= 3 ,B真实用量=null UNION ALL
select 级别= 3 ,A='C1',B='D2',用量= 2 ,B真实用量=null UNION ALL
select 级别= 3 ,A='C1',B='D3',用量= 2 ,B真实用量=null UNION ALL
select 级别= 3 ,A='C1',B='D4',用量= 5 ,B真实用量=null UNION ALL
select 级别= 4 ,A='D1',B='E1',用量= 9 ,B真实用量=null UNION ALL
select 级别= 4 ,A='D2',B='E2',用量= 5 ,B真实用量=null ) t
;WITH list AS (SELECT 级别, A, B, 用量, B真实用量 = 用量 FROM #t WHERE 级别 = 1
UNION ALL
SELECT t.级别, t.A, t.B, t.用量, l.B真实用量 * t.用量
FROM #t t
INNER JOIN list l ON l.级别 = t.级别 - 1 AND l.B = t.A)
SELECT * FROM list ORDER BY list.级别, list.A;
WITH CTE
AS
(SELECT *,QTY AS NEW_QTY FROM #T A
WHERE NOT EXISTS (SELECT 1 FROM #T WHERE LEVEL<A.LEVEL AND B=A.A)
UNION ALL
SELECT A.*,B.NEW_QTY*A.QTY
FROM #T A
JOIN CTE B ON A.A=B.B
WHERE A.LEVEL>B.LEVEL)
SELECT * FROM CTE
ORDER BY LEVEL,A