一个统计视图，如何实现？

[sxbug]

这个问题，我以前提过，在高人的指点下，做了一个，现在又发现新问题了。问题是：
一个wgpecost表存放，每个月所有人员的水电、煤气等其他费用每月的应交数目
CustomerID CC_Date water power gas
001 2005-10-2 12 100.5 70.2
001 2005-11-8 14 78 90
002 2005-11-8 32 47 10

另一个表wgpepay存放 每个月所有人员实缴的费用
CustomerID PAY_DATE w_p p_p G_P
001 2005-11-8 12 100 70
002 2005-11-8 4 20 30

我需要有一个分时间段的统计视图，如截至2005-11 001号用户水费多少，已缴多少，欠多少，合计欠费多少？

问题是：用户可能会有增减。
这样的视图如何实现？
谢谢

[sxbug]

这个是我以前写的视图，没有整理，直接贴出来。这个视图的问题就是增加的人员，数据不对。
SELECT N.EMPNO, NVL(PAYID,'-') PAYID, COSTID,GMETER, GMETER_P, GUNITCOST, G_COST, G_USEVALUE, M_COST, PMETER, PMETER_P,
PUNITCOST, P_COST, P_USEVALUE, WMETER, WMETER_P, WUNITCOST, W_COST, W_USEVALUE, Y_COST,
CHECKER,
TO_CHAR(CHECK_DATE,'YYYY-MM') C_DATE,

NVL(M.G_PAY,0) G_PAY, NVL(M.M_PAY,0) M_PAY, NVL(M.P_PAY,0) P_PAY,
NVL(M.W_PAY,0) W_PAY, NVL(M.Y_PAY,0) Y_PAY,NVL(M.REC_PEOPLE,'-') REC_PEOPLE,NVL(M.CHARGER,'-') CHARGER,

(SELECT SUM(W_COST) FROM JAMIS.WGPECOST WHERE CHECK_DATE<=N.CHECK_DATE) W_TOTALCOST,
(SELECT SUM(G_COST) FROM JAMIS.WGPECOST WHERE CHECK_DATE<=N.CHECK_DATE) G_TOTALCOST,
(SELECT SUM(M_COST) FROM JAMIS.WGPECOST WHERE CHECK_DATE<=N.CHECK_DATE) M_TOTALCOST,
(SELECT SUM(P_COST) FROM JAMIS.WGPECOST WHERE CHECK_DATE<=N.CHECK_DATE) P_TOTALCOST,
(SELECT SUM(Y_COST) FROM JAMIS.WGPECOST WHERE CHECK_DATE<=N.CHECK_DATE) Y_TOTALCOST,
(SELECT SUM(Y_COST+W_COST+G_COST+P_COST+M_COST) FROM JAMIS.WGPECOST WHERE CHECK_DATE<=N.CHECK_DATE) ALL_TOTALCOST,

NVL((SELECT SUM(W_COST-W_PAY) FROM JAMIS.WGPECOST,JAMIS.WGPEPAY WHERE JAMIS.WGPECOST.CHECK_DATE<=N.CHECK_DATE AND JAMIS.WGPECOST.EMPNO=JAMIS.WGPEPAY.EMPNO AND JAMIS.WGPECOST.CHECK_DATE=JAMIS.WGPEPAY.PAY_DATE),0) W_TOTALNOPAY,
NVL((SELECT SUM(G_COST-G_PAY) FROM JAMIS.WGPECOST,JAMIS.WGPEPAY WHERE JAMIS.WGPECOST.CHECK_DATE<=N.CHECK_DATE AND JAMIS.WGPECOST.EMPNO=JAMIS.WGPEPAY.EMPNO AND JAMIS.WGPECOST.CHECK_DATE=JAMIS.WGPEPAY.PAY_DATE),0) G_TOTALNOTPAY,
NVL((SELECT SUM(M_COST-M_PAY) FROM JAMIS.WGPECOST,JAMIS.WGPEPAY WHERE JAMIS.WGPECOST.CHECK_DATE<=N.CHECK_DATE AND JAMIS.WGPECOST.EMPNO=JAMIS.WGPEPAY.EMPNO AND JAMIS.WGPECOST.CHECK_DATE=JAMIS.WGPEPAY.PAY_DATE),0) M_TOTALNOTPAY,
NVL((SELECT SUM(P_COST-P_PAY) FROM JAMIS.WGPECOST,JAMIS.WGPEPAY WHERE JAMIS.WGPECOST.CHECK_DATE<=N.CHECK_DATE AND JAMIS.WGPECOST.EMPNO=JAMIS.WGPEPAY.EMPNO AND JAMIS.WGPECOST.CHECK_DATE=JAMIS.WGPEPAY.PAY_DATE),0) P_TOTALNOTPAY,
NVL((SELECT SUM(Y_COST-Y_PAY) FROM JAMIS.WGPECOST,JAMIS.WGPEPAY WHERE JAMIS.WGPECOST.CHECK_DATE<=N.CHECK_DATE AND JAMIS.WGPECOST.EMPNO=JAMIS.WGPEPAY.EMPNO AND JAMIS.WGPECOST.CHECK_DATE=JAMIS.WGPEPAY.PAY_DATE),0) Y_TOTALNOTPAY,

NVL(JAMIS.CUSTOMERS.USERNAME,'-') USERNAME,NVL(JAMIS.CUSTOMERS.ADDRESS,'-') ADDRESS,
NVL(JAMIS.CUSTOMERS.PHONE_HOME,'-') PHONE_HOME,NVL(JAMIS.CUSTOMERS.PHONE_MOBILE,'-') PHONE_MOBILE,
NVL(JAMIS.CUSTOMERS.HOUSE_NO,'-') HOUSE_NO, NVL(JAMIS.CUSTOMERS.HOUSE_TYPE,'-') HOUSE_TYPE,
NVL(JAMIS.CUSTOMERS.DEPARTMENT,'-') DEPARTMENT,NVL(JAMIS.CUSTOMERS.EMPNO_INNER,'-') EMPNO_INNER
FROM JAMIS.WGPECOST N, JAMIS.WGPEPAY M, JAMIS.CUSTOMERS
WHERE N.EMPNO=M.EMPNO(+) AND TO_CHAR(N.CHECK_DATE,'YYYY-MM')=TO_CHAR(M.PAY_DATE(+),'YYYY-MM')
AND N.EMPNO=JAMIS.CUSTOMERS.CUSTOMERID

[yxh1214]

create view aa as
select a.CustomerID , a.ny 年月,a.w_water 水费,a.w_power 电费,a.w_gas 气费,(a.w_water+a.w_power+a.w_gas) as 应交费,
b.w_p 已交水,b.p_p 已交电,b.G_P 已交气, (b.w_p+b.p_p+b.G_P) 已交费,(a.w_water+a.w_power+a.w_gas)-(b.w_p+b.p_p+b.G_P) 未交费
from
(select CustomerID,to_char(cc_date,'yyyy-mm') as ny,sum(water) as w_water ,sum(power) as w_power,sum(gas) as w_gas
from wgpecost
group CustomerID,to_char(cc_date,'yyyy-mm') a,wgpepay b
where a.ny=to_char(cc_date,b.PAY_DATE'yyyy-mm') and a.CustomerID=b.CustomerID

[yxh1214]

合计欠费:在新建的这个视图上进一步求和即可．．

[sinusoid]

Select 0 Water_Cost, --用户欠水费
0 Water_Pay, --用户欠水费
0 Water_Less, --当月欠费
(Select Sum(w_p) - Sum(Water)
From Wgpecost, Wgpepay
Where Wgpecost.Customerid = Wgpepay.Customerid
And (Trunc(Cc_Date, 'yy') || Trunc(Cc_Date, 'mm') < '2005-11' And
Trunc(Pay_Date, 'yy') || Trunc(Pay_Date, 'mm') < '2005-11'))) Total_Water_Less --累计欠费
From (Select Water_Cost, --用户水费多少
Water_Pay, --已付水费
Water_Cost - Water_Pay Water_Less, --当月欠费
0 Total_Water_Less --累计欠费
From (Select Sum(Water) Water_Cost --应付水费
0 Water_Pay, --已付水费
From Wgpecost
Where Trunc(Cc_Date, 'yy') || Trunc(Cc_Date, 'mm') =
'2005-11'
Group By Customerid
Union All
Select 0 Water_Cost --应付水费
Sum(w_p) Water_Pay --已付水费
From Wgpepay
Where Trunc(Pay_Date, 'yy') || Trunc(Pay_Date, 'mm') =
'2005-11'))