从多个表中显示帐户余额 [英] Show account balance from multiple tables

问题描述

我有以下两个表，用于存储有关信用卡和借记记录的信息.

I am having following two table which stores information about Credit and Debit records.

voucherCr 表包含

voucherCr table contains

voucherType    voucherPrefix    voucherNo    crparty    cramount
SALES          S                1            1          43000
SALES          S                2            1          10000

voucherDr 表包含

voucherDr table contains

voucherType    voucherPrefix    voucherNo    drparty    dramount
SALES          S                1            5          43000
SALES          S                2            5          10000

现在在这里，在 SALES 凭证 S/1 中，第 1 方已向第 5 方贷记了 43000 的金额.销售凭证 S/2 也是如此，其中第一方已被记入 10000 的金额，而第五方的金额相同.

Now here, in SALES voucher S/1, party 1 has been credit with 43000 amount agains party 5 of same amount. Same is with SALES voucher S/2, where party 1 has been credited with 10000 amount against party 5 of same amount.

现在我想显示如下结果 如果我查询第 1 方

Now I want to display results as follows If i query about party 1

PARTY    CREDIT    DEBIT    DEBITPARTY    voucherType    voucherPrefix    voucherNo
1        43000              5              SALES          S                1
1        10000              5              SALES          S                2

请帮忙

推荐答案

如果我已经正确理解了您的问题，那么这就是您要寻找的

If i have understood your question properly then this is what you are looking for

Select c.crParty as Party, d.dramount as credit , null as debit,
d.drParty as DEBITPARTY,c.voucherType as voucherType,
d.voucherPrefix,d.voucherNo
from VoucherCr as c inner join VoucherDr as d
on c.voucherNo=d.VoucherNo and c.voucherPrefix=d.voucherPrefix
where c.crparty=1
group by d.dramount,c.cramount,d.voucherPrefix,d.voucherNo,c.crParty,
c.voucherType,d.drParty
order by d.dramount desc

试试SQLFIDDLE

这篇关于从多个表中显示帐户余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！