请教一个sql语句

[会飞的小洋洋]

两个表：
分类表：Category(ID int,ParentID int)
数量表：ProductSum(ID int,type int,Amount int)
分类表有归属关系通过ParentID区分一二两级分类，一级分类的ParentID=null
数量表的ID就是分类ID，Amount表示分类下商品数量，type是一个标志
数量表中ID可能重复出现，但重复出现时多条记录的type是不同的
现在要更新所有一级分类的Amount为其下二级分类Amount的和，但要用type区分

A:
ID ParentID
1 null
2 null
3 1
4 2
5 1

B:
ID type Amount
1 1 0 <-更新后这个Amount应该为 2+3
3 1 2
5 1 3
1 2 0 <-更新后这个Amount应该为4
3 2 4
2 1 0 <-更新后这个Amount应该为2
4 1 2
2 2 0 <-更新后这个Amount还是0

(不用理会原来数量表中一级分类的数量，用一条SQL语句实现)

[paoluo]

Create Table Category(ID int,ParentID int)
Insert Category Select 1, null
Union All Select 2, null
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 1

Create Table ProductSum(ID int,type int,Amount int)
Insert ProductSum Select 1, 1, 0
Union All Select 3, 1, 2
Union All Select 5, 1, 3
Union All Select 1, 2, 0
Union All Select 3, 2, 4
Union All Select 2, 1, 0
Union All Select 4, 1, 2
Union All Select 2, 2, 0
GO
Update
A
Set
Amount = IsNull((Select SUM(Amount) From ProductSum B Inner Join Category C On B.ID = C.ID Where ParentID = A.ID And type = A.type ), Amount)
From
ProductSum A

Select * From ProductSum
GO
Drop Table ProductSum, Category

--Result
/*
ID type Amount
1 1 5
3 1 2
5 1 3
1 2 4
3 2 4
2 1 2
4 1 2
2 2 0
*/

[paoluo]

改用關聯來寫

Create Table Category(ID int,ParentID int)
Insert Category Select 1, null
Union All Select 2, null
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 1

Create Table ProductSum(ID int,type int,Amount int)
Insert ProductSum Select 1, 1, 0
Union All Select 3, 1, 2
Union All Select 5, 1, 3
Union All Select 1, 2, 0
Union All Select 3, 2, 4
Union All Select 2, 1, 0
Union All Select 4, 1, 2
Union All Select 2, 2, 0
GO
Update
A
Set
Amount = IsNull(D.Amount, A.Amount)
From
ProductSum A
Left Join
(Select C.ParentID, B.type, SUM(B.Amount) As Amount From ProductSum B Inner Join Category C On B.ID = C.ID Group By C.ParentID, B.type ) D
On A.ID = D.ParentID And A.type = D.type

Select * From ProductSum
GO
Drop Table ProductSum, Category

--Result
/*
ID type Amount
1 1 5
3 1 2
5 1 3
1 2 4
3 2 4
2 1 2
4 1 2
2 2 0
*/