求视图编写方法,要按层次进行统计.

Question

用户在统计是要根据物资层次,每级都要进行数量统计.例如: 
物资表
物资代码 上层代码
A                  0
E                   A
E01               E
E02               E数据表
E01               3
E02               4期望查询结果为：
A                   7
E                    7
E01                3
E02                 4

Answer 1

--测试数据
CREATE TABLE 物资表(物资代码 varchar(10), 层代码 varchar(10))
INSERT 物资表
SELECT 'A', '0' UNION ALL
SELECT 'E', 'A' UNION ALL
SELECT 'E01', 'E' UNION ALL
SELECT 'E02', 'E' CREATE TABLE 数据表(物资代码 varchar(10), value int)
INSERT 数据表 
SELECT 'E01', 3 UNION ALL
SELECT 'E02', 4
GO-- 汇总处理
SELECT 
A.物资代码, A.层代码,
SUM_VALUE = ISNULL(SUM(B.value), 0),
flag = 0
INTO #
FROM 物资表 A
LEFT JOIN 数据表 B
ON A.物资代码 = B.物资代码
GROUP BY A.物资代码, A.层代码
UPDATE A SET 
flag = 1
FROM # A
WHERE NOT EXISTS(
SELECT * FROM # WHERE 层代码 = A.物资代码)
WHILE @@ROWCOUNT > 0
BEGIN
UPDATE A SET
flag = 1,
SUM_VALUE = A.SUM_VALUE + B.SUM_VALUE
FROM # A, (
SELECT 层代码, SUM_VALUE = SUM(SUM_VALUE)
FROM # A
WHERE flag = 1
AND NOT EXISTS(
SELECT * FROM # WHERE flag = 0 AND 层代码 = A.层代码
)
GROUP BY 层代码
)B
WHERE A.flag = 0
AND A.物资代码 = B.层代码
END
SELECT 物资代码, SUM_VALUE FROM #
DROP TABLE #
GODROP TABLE 数据表, 物资表-- 结果:
物资代码       SUM_VALUE
---------- -----------
A          7
E          7
E01        3
E02        4(4 行受影响)

Answer 2

zjcxc(邹建)兄说的有一个问题。
就是在物资表中最底层物资，没有出现在数据表中的话，其上层也是要统计的。
有没有更好的方法？