I'm thinking of building query from these 2 tables (on SQL Server 2008). I have 2 tables as shown below:
Table 1
MemberId . MemberName . Percentage . Amount1
00000001 AAA 1.0 100
00000002 BBB 1.2 800
00000003 ZZZ 1.0 700
Table 2
MemberId . MemberName . Percentage . Amount2
00000002 BBB 1.5 500
00000002 BBB 1.6 开发者_开发问答 100
00000002 BBB 1.6 150
The result I want is
MemberId . MemberName . Percentage . Amount . NettAmount
00000001 AAA 1.0 100 100
00000002 BBB 1.2 800 50 <-- 800-(500+100+150)
00000002 BBB 1.5 500 500
00000002 BBB 1.6 250 250
00000003 ZZZ 1.0 700 700
50 comes from 800 in Table1 minus sum of Amount2 in table2 for MemberID=00000002
Plz someone help me to build the query to reach this result.
Thank you in advance.
What you need to do is something like this:
- Select from Table1
- Do a GROUP BY select from Table2
- Join the results of those two queries by MemberId
SQL code:
SELECT ... FROM Table1
INNER JOIN (SELECT MemberId, SUM(Amount2) FROM Table2 GROUP BY MemberId) Agg
ON Table1.MemberId = Agg.MemberId
Then you should be able to select "Amount1 - Amount2" from the join.
HTH.
精彩评论