還是這個Select
A.贵宾卡号,
A.积分 + IsNull(B.积分, 0)
From
A
Left Join
(Select 贵宾卡号, SUM(积分) As 积分 Group By 贵宾卡号) B
On A.贵宾卡号 = B.贵宾卡号
A.贵宾卡号,
A.积分 + IsNull(B.积分, 0)
From
A
Left Join
(Select 贵宾卡号, SUM(积分) As 积分 Group By 贵宾卡号) B
On A.贵宾卡号 = B.贵宾卡号
A.贵宾卡号,
A.积分 + IsNull(SUM(B.积分), 0)
From
A
Left Join
B
On A.贵宾卡号 = B.贵宾卡号
Group By
A.贵宾卡号,
A.积分
Create Table A
(贵宾卡号 Nvarchar(20),
积分 Int)
Insert A Select '66601636', 69
Union All Select '66600283', 158
Union All Select '66600351', 119
Union All Select '66601329', 455Create Table B
(贵宾卡号 Nvarchar(20),
积分 Int)
Insert B Select '66601636', 20
Union All Select '66601636', 10
Union All Select '66600351', 80
Union All Select '66600351', -20
Union All Select '66601636', -50
Union All Select '66600283', 40
Union All Select '66601329', -30
Union All Select '66601329', 20
Union All Select '66600283', 60
Union All Select '66600351', 10
Union All Select '66600351', 70
Union All Select '66601329', -20
Union All Select '66601329', -80
GO
--測試
Select
A.贵宾卡号,
A.积分 + IsNull(B.积分, 0) As 积分
From
A
Left Join
(Select 贵宾卡号, SUM(积分) As 积分 From B Group By 贵宾卡号) B
On A.贵宾卡号 = B.贵宾卡号
Order By
A.贵宾卡号Select
A.贵宾卡号,
A.积分 + IsNull(SUM(B.积分), 0) As 积分
From
A
Left Join
B
On A.贵宾卡号 = B.贵宾卡号
Group By
A.贵宾卡号,
A.积分
Order By
A.贵宾卡号
GO
--刪除測試環境
Drop Table A, B
--結果
/*
贵宾卡号 积分
66600283 258
66600351 259
66601329 345
66601636 49
*/