update A set A.amount=B.amount
where A.id in (select id from C where id in (B.id))
B.id值是多个子查询C.id也是多个。B表与C表的关系是一对多（上下层级关系），A表中的id包含了所有的id。
我执行代码时，仅更新了B.id的第一个值。请问如何根据B.id进行多次数据更新？
------解决思路----------------------
/*测试数据
;WITH ta(id,amount) AS (
SELECT 'C',0 UNION ALL
SELECT 'C.01',0 UNION ALL
SELECT 'C.01.01',0 UNION ALL
SELECT 'C.01.02',0 UNION ALL
SELECT 'C.02',0
)
SELECT * INTO a FROM ta
;WITH tb(id,amount) AS (
SELECT 'C.01.01',2 UNION ALL
SELECT 'C.01.02',3 UNION ALL
SELECT 'C.02',4
)
SELECT * INTO b FROM tb
;WITH tc(pid,cid) AS (
SELECT 'C','C.01' UNION ALL
SELECT 'C','C.02' UNION ALL
SELECT 'C.01','C.01.01' UNION ALL
SELECT 'C.01','C.01.02'
)
SELECT * INTO c FROM tc
GO
*/
CREATE TABLE #temp(
id varchar(10),
amount int,
level int
)
DECLARE @level int
SET @level = 1
INSERT INTO #temp
SELECT id, amount, @level
FROM b
WHILE @@ROWCOUNT<>0
BEGIN
SET @level = @level+1
INSERT INTO #temp
SELECT c.pid, SUM(t.amount), @level
FROM #temp t
JOIN c
ON c.cid = t.id
WHERE t.level = @level-1
GROUP BY c.pid
END
SELECT * FROM #temp ORDER BY id,level
UPDATE a
SET a.amount = t.amount
FROM a,
(
SELECT id, SUM(amount) amount
FROM #temp
GROUP BY id
) t
WHERE a.id = t.id
SELECT * FROM a
DROP TABLE #temp
id amount level
---------- ----------- -----------
C 4 2
C 5 3
C.01 5 2
C.01.01 2 1
C.01.02 3 1
C.02 4 1
id amount
------- -----------
C 9
C.01 5
C.01.01 2
C.01.02 3
C.02 4