EPAY--->EBILL----->EPAY_DETAIL
主 明細 細細
你的三個表是主細細的關系。
你現在要統計“已付金额”,那只需要管EPAY_DETAIL就行了。
因為EPAY_DETAIL中就是已付金额.
主 明細 細細
你的三個表是主細細的關系。
你現在要統計“已付金额”,那只需要管EPAY_DETAIL就行了。
因為EPAY_DETAIL中就是已付金额.
调试欢乐多
主 明細 細細
你的三個表是主細細的關系。
你現在要統計“已付金额”,那只需要管EPAY_DETAIL就行了。
因為EPAY_DETAIL中就是已付金额.
如果可以的话,那就专门对这张表操作就是了。。
没有那么多的信息.他只有:
pay_id发票ID
INVOICE_NO发票号
PAY_SUM已付金额(付款的明细)
所以,要其它表一起来作统计.
这就是:
讓EPAY關聯EBILL,EPAY關聯EPAY_DETAIL,而EBILL和EPAY_DETAIL不要關聯。
select a.Pay_Id,a.FOOD_CORP,a.CURR_YEAR,a.CURR_MONTH,a.INVOICE_NO,
nvl(a.INVOICE_SUM,0) as INVOICE_SUM,
nvl(b.bzTOTAL,0) as zTOTAL,
nvl(b.bZFACT_SUM,0) as ZFACT_SUM,
nvl(b.bZPROTEST_SUM,0) as ZPROTEST_SUM,
nvl(c.cZPAY_SUM,0) as ZPAY_SUM,
nvl(b.bZFACT_SUM,0) - nvl(c.cZPAY_SUM,0) as ZNOT_PAY_SUM
from EPAY a,
(select Pay_Id,
sum(nvl(TOTAL,0)) as bzTOTAL,
sum(nvl(FACT_SUM,0)) as bZFACT_SUM,
sum(nvl(PROTEST_SUM,0)) as bZPROTEST_SUM
from EBILL
group by Pay_Id) b,
(select Pay_id,
sum(nvl(PAY_SUM,0)) as cZPAY_SUM
from EPAY_DETAIL
group by Pay_id) c
where (a.pay_id=b.pay_id(+)) and (a.pay_id=c.pay_id(+))
order by a.FOOD_CORP,a.CURR_YEAR,a.CURR_MONTH,a.INVOICE_NO,a.INVOICE_SUM
select a.Pay_Id,a.FOOD_CORP,a.CURR_YEAR,a.CURR_MONTH,a.INVOICE_NO,
nvl(a.INVOICE_SUM,0) as INVOICE_SUM,
nvl(b.bzTOTAL,0) as zTOTAL,
nvl(b.bZFACT_SUM,0) as ZFACT_SUM,
nvl(b.bZPROTEST_SUM,0) as ZPROTEST_SUM,
nvl(c.cZPAY_SUM,0) as ZPAY_SUM,
nvl(b.bZFACT_SUM,0) - nvl(c.cZPAY_SUM,0) as ZNOT_PAY_SUM
from EPAY a,
(select Pay_Id,
sum(nvl(TOTAL,0)) as bzTOTAL,
sum(nvl(FACT_SUM,0)) as bZFACT_SUM,
sum(nvl(PROTEST_SUM,0)) as bZPROTEST_SUM
from EBILL
group by Pay_Id) b,
(select Pay_id,
sum(nvl(PAY_SUM,0)) as cZPAY_SUM
from EPAY_DETAIL
group by Pay_id) c
where (a.pay_id=b.pay_id(+)) and (a.pay_id=c.pay_id(+))
order by a.FOOD_CORP,a.CURR_YEAR,a.CURR_MONTH,a.INVOICE_NO,a.INVOICE_SUM