两个表:
分类表: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 1B:
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语句实现)
分类表: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 1B:
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语句实现)
Insert Category Select 1, null
Union All Select 2, null
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 1Create 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 ASelect * 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
*/
Insert Category Select 1, null
Union All Select 2, null
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 1Create 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.typeSelect * 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
*/