SQL查询，根据以下条件合并三个表 [英] Sql Query, to merge three tables according the following condition
问题描述
我有三个表，第一个表是计费表，该表中的字段是
1)结算
BillingDate帐单编号客户代码付款
2011-05-02 EB-1243 SAIN40 12945.23
2011-05-02 EB-1243 SAIN40 0
2011-05-02 EB-1243 SAIN40 0
2011-05-02 EB-1243 SAIN40 0
2011-05-09 EB-1250 SAIN40 14445.49
2011-05-09 EB-1250 SAIN40 0
2011-05-09 EB-1250 SAIN40 0
2011-05-09 EB-1250 SAIN40 0
2011-05-17 EB-1260 SAIN40 5790.75
2011-05-17 EB-1260 SAIN40 0
2011-05-17 EB-1260 SAIN40 0
2011-05-18 EB-1260 SAIN40 0
2011-05-18 EB-1270 SAIN40 4325
2011-05-18 EB-1270 SAIN40 0
2011-05-18 EB-1270 SAIN40 0
2011-05-18 EB-1270 SAIN40 0
2011-05-18 EB-1270 SAIN40 0
2)收据
收据日期收据编号客户代码Ammount
2011-08-09 Rct:BK-4517 SAIN40 60,0000
第三个表是
3)客户
CustCode LastDue
SAIN40 37634.26
现在，我们要根据以下条件合并此表:
1)根据客户代码(客户代码)合并表格.
2)并像这样生成新字段BillAmt
BillAmt 收款金额调整
37634.26(客户第3表最后到期)60,000.00 -22,366.00
12945(从第一张桌子付款，计费)22,366.00 -9,421.00
14445.49(从第一张桌子付款，计费)9,421.00 5,024.49
5790(从第一张桌子付款，账单)5,790.00
4325(从第一张桌子付款，计费)4,325.00
3) RecvdAmt调整
60,000.00 -22,366.00
22,366.00 -9,421.00
9,421.00 5,024.49
............ 5,790.00
..... 4,325.00
根据第二张表减去来自客户表的最后到期的37634.26(60000-37634 = 22,366)，得出总的剩余金额为60,000，然后剩余的总金额为22,366.并且第一张帐单付款是从第一张表s0中提取的12945(22,366-12,945 = 9,421.00)，第二张帐单是14445.49，然后(9,421-14445.49 = -5024)，因此在第三列中生成了调整字段.....
编辑-
[在解决方案部分将OP的注释移至此处.]
但是，我想要这样的结果. -
3)RecvdAmt调整
60,000.00 -22,366.00
22,366.00 -9,421.00
9,421.00 5,024.49
............ 5,790.00
..... 4,325.00
希望您能理解我的问题，所以请帮助我.
谢谢，
I have a three tables the first table is billing table the field of in this table is
1)Billing
BillingDate Billnumber Custcode Payment
2011-05-02 EB-1243 SAIN40 12945.23
2011-05-02 EB-1243 SAIN40 0
2011-05-02 EB-1243 SAIN40 0
2011-05-02 EB-1243 SAIN40 0
2011-05-09 EB-1250 SAIN40 14445.49
2011-05-09 EB-1250 SAIN40 0
2011-05-09 EB-1250 SAIN40 0
2011-05-09 EB-1250 SAIN40 0
2011-05-17 EB-1260 SAIN40 5790.75
2011-05-17 EB-1260 SAIN40 0
2011-05-17 EB-1260 SAIN40 0
2011-05-18 EB-1260 SAIN40 0
2011-05-18 EB-1270 SAIN40 4325
2011-05-18 EB-1270 SAIN40 0
2011-05-18 EB-1270 SAIN40 0
2011-05-18 EB-1270 SAIN40 0
2011-05-18 EB-1270 SAIN40 0
2)Receipts
ReceiptDate ReceiptNo. Custcode Ammount
2011-08-09 Rct:BK-4517 SAIN40 60,0000
and the third table is
3)Customer
CustCode LastDue
SAIN40 37634.26
now, we want to merge this table according the following condition-:
1)merge the table according the custcode(customercode).
2)and generate the new field BillAmt like this
BillAmt ReceivedAmount Adjustment
37634.26(LastDue from 3rd table, Customer) 60,000.00 -22,366.00
12945 (Payment from 1st table,Billing) 22,366.00 -9,421.00
14445.49 (Payment from 1st table,Billing) 9,421.00 5,024.49
5790 (Payment from 1st table,Billing) 5,790.00
4325 (Payment from 1st table,Billing) 4,325.00
3)RecvdAmt Adjustment
60,000.00 -22,366.00
22,366.00 -9,421.00
9,421.00 5,024.49
............ 5,790.00
.......... . 4,325.00
it will be genrate The total recive ammount is 60,000 according the 2nd table minus the last due 37634.26 from customer table(60000-37634=22,366) and then total left ammount is 22,366. and the 1st billing payment is 12945 from 1st table s0 (22,366-12,945=9,421.00), the second billing is 14445.49 then (9,421-14445.49=-5024) so the adjustment field generate in the 3rd column.....
EDIT -
[Moved OP''s comments here from solution section.]
But, I want the result like this. -
3)RecvdAmt Adjustment
60,000.00 -22,366.00
22,366.00 -9,421.00
9,421.00 5,024.49
............ 5,790.00
.......... . 4,325.00
I hope you understand my problem so, please help me.
Thanks,
推荐答案
尝试此查询..
Try this Query..
SELECT A.Custcode as 'Customer Code', (C.LastDue) as 'BillAmt',
B.Ammount as 'ReceivedAmount', (B.Ammount-C.LastDue) as 'Adjustment' FROM
Billing as A INNER JOIN Receipts as B ON A.Custcode = B.Custcode
INNER JOIN Customer as C ON C.Custcode = A.Custcode
感谢Karthik，
但是，我想要这样的结果...........
3)RecvdAmt调整
60,000.00 -22,366.00
22,366.00 -9,421.00
9,421.00 5,024.49
............ 5,790.00
..... 4,325.00
Thanks Karthik,
But, I want the result like this...........
3)RecvdAmt Adjustment
60,000.00 -22,366.00
22,366.00 -9,421.00
9,421.00 5,024.49
............ 5,790.00
.......... . 4,325.00
这篇关于SQL查询，根据以下条件合并三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
