Sorry,打漏了。应该是: update A set A.amount=B.amount from a,b where A.id in (select id from C where id in (B.id)) 我想更新A表的值,A表是全部科目的数据,B表是末级科目的表。C表是A,B表的关联表。通过C.id可以查到属于B.id的上级全部上级科目A.id。这样来更新A表上级的值。 示例: A表 科目GUID 金额 C 0 C.01 0 C.01.01 0 C.01.02 0 C.02 0 B表 C.01.01 2 C.01.02 3 C.02 4 C表 科目GUID 末级科目GUID C C.01 C C.01 C.01 C.01.01 C.01 C.01.02 我想得到的结果是: C 9 C.01 5 C.01.01 2 C.01.02 3 C.02 4 不知道各位大神明白了题目了没?
/*测试数据 ;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 = 1INSERT INTO #temp SELECT id, amount, @level FROM bWHILE @@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 ENDSELECT * FROM #temp ORDER BY id,levelUPDATE a SET a.amount = t.amount FROM a, ( SELECT id, SUM(amount) amount FROM #temp GROUP BY id ) t WHERE a.id = t.idSELECT * FROM aDROP 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
update A set A.amount=B.amount
from a,b
where A.id in (select id from C where id in (B.id))
我想更新A表的值,A表是全部科目的数据,B表是末级科目的表。C表是A,B表的关联表。通过C.id可以查到属于B.id的上级全部上级科目A.id。这样来更新A表上级的值。
示例:
A表
科目GUID 金额
C 0
C.01 0
C.01.01 0
C.01.02 0
C.02 0
B表
C.01.01 2
C.01.02 3
C.02 4
C表
科目GUID 末级科目GUID
C C.01
C C.01
C.01 C.01.01
C.01 C.01.02
我想得到的结果是:
C 9
C.01 5
C.01.01 2
C.01.02 3
C.02 4
不知道各位大神明白了题目了没?
感觉用这个update实现不到,必须要用其他方式,递归、游标什么的把update遍历一次是吗?
麻烦回复的时候具体点,好久没写代码了,不太会了,先谢过了哈
;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 = 1INSERT INTO #temp
SELECT id, amount, @level
FROM bWHILE @@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
ENDSELECT * FROM #temp ORDER BY id,levelUPDATE a
SET a.amount = t.amount
FROM a,
(
SELECT id, SUM(amount) amount
FROM #temp
GROUP BY id
) t
WHERE a.id = t.idSELECT * FROM aDROP 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