已有充值表和消费表,要求统计每个月的卡充值总额和这些卡至今的消费总额。举例如下:
充值表(CZ):
卡号(cardno) 充值日期(loddate) 充值金额(lodamt)
91034567 2010-2-1 100
91034567 2010-5-22 200
91045678 2010-2-11 300
91056789 2010-3-2 200
消费表(XF):
卡号(cardno) 面值(blance) 消费金额(amt) 消费日期(date)
91034567 100 40.00 2010-2-15
91034567 100 60.00 2010-5-2
91034567 200 120.00 2010-10-3
91045678 300 128.00 2010-10-3
91045678 300 150.00 2011-5-2
91056789 200 75.00 2010-4-6
。
统计2月份的数据如下:
充值总额(lodall)= 400(100+300 )
消费总额(amtall)= 378(40+60+128+150)
统计3月份数据。统计每个月的充值总额没有问题,但统计每个月的消费总额就比较困难了。因为卡片可以回收,所以存在一个卡号重复使用(如91034567,第一次2月份充值后全部消费完,第二次5月份充值后只消费了120元),请各位大侠帮助一下,怎么写这个统计语句?
充值表(CZ):
卡号(cardno) 充值日期(loddate) 充值金额(lodamt)
91034567 2010-2-1 100
91034567 2010-5-22 200
91045678 2010-2-11 300
91056789 2010-3-2 200
消费表(XF):
卡号(cardno) 面值(blance) 消费金额(amt) 消费日期(date)
91034567 100 40.00 2010-2-15
91034567 100 60.00 2010-5-2
91034567 200 120.00 2010-10-3
91045678 300 128.00 2010-10-3
91045678 300 150.00 2011-5-2
91056789 200 75.00 2010-4-6
。
统计2月份的数据如下:
充值总额(lodall)= 400(100+300 )
消费总额(amtall)= 378(40+60+128+150)
统计3月份数据。统计每个月的充值总额没有问题,但统计每个月的消费总额就比较困难了。因为卡片可以回收,所以存在一个卡号重复使用(如91034567,第一次2月份充值后全部消费完,第二次5月份充值后只消费了120元),请各位大侠帮助一下,怎么写这个统计语句?
月份 lodall amtall
2 400 378
3 200 75
5 200 120
CARDNO LODDATE LODAMT
-------- ----------- ----------
91034567 2010-2-1 100
91034567 2010-5-22 200
91045678 2010-2-11 300
91056789 2010-3-2 200
SQL> select * from xf;
CARDNO BLANCE AMT PAYDATE
-------- ---------- ---------- -----------
91034567 100 40 2010-2-15
91034567 100 60 2010-5-2
91034567 200 120 2010-10-3
91045678 300 128 2010-10-3
91045678 300 150 2011-5-2
91056789 200 75 2010-4-6
6 rows selected
SQL>
SQL> SELECT to_char(loddate, 'yyyymm') loddate, SUM(lodamt) lodamt, SUM(amt) amt
2 FROM (SELECT cz.*,
3 (SELECT SUM(amt)
4 FROM xf
5 WHERE cz.cardno = xf.cardno
6 AND cz.lodamt = xf.blance
7 AND cz.loddate <= xf.paydate) amt
8 FROM cz)
9 GROUP BY to_char(loddate, 'yyyymm');
LODDATE LODAMT AMT
------- ---------- ----------
201002 400 378
201003 200 75
201005 200 120
SQL>