已有充值表和消费表，要求统计每个月的卡充值总额和这些卡至今的消费总额。举例如下：
充值表（CZ）：
卡号（cardno） 充值次数（times） 充值日期（loddate） 充值金额（lodamt）
91034567 1 2010-2-12 100
91034567 2 2010-5-21 100
91034567 3 2010-9-16 200
91045678 1 2010-3-1 200
91045678 2 2010-3-23 300
91056789 1 2010-2-19 300
。。。。。。
消费表(XF):
卡号（cardno) 面值（blance） 消费金额（amt） 消费日期（date）
91034567 100 40.00 2010-2-15
91034567 100 60.00 2010-3-26
91034567 100 100.00 2010-8-11
91034567 100 160.00 2010-10-3
91045678 200 120.00 2010-3-1
91045678 200 80.00 2010-3-5
91045678 200 220.00 2010-5-15
91056789 300 278.00 2010-5-2
。。。。。。。
统计2月份的数据如下：
充值总额（lodall）= 400（100+300 ）
消费总额（amtall）= 378（40+60+278）
统计3月份数据
充值总额（lodall）= 500（200+300 ）
消费总额（amtall）= 200（120+80）
。。。。。。。
要求格式如下：
LODDATE LODALL AMTALL
------- ---------- ----------
201002 400 378
201003 500 200
。。。。。。
统计每个月的充值总额没有问题，但统计每个月的消费总额就比较困难了。因为卡片可以回收，所以存在一个卡号重复使用（如91034567，第一次2月份充值后全部消费完，第二次5月份充值又使用了），请各位大侠帮助一下，怎么写这个统计语句？
注意三点：
1、不是月度统计，是统计卡片从充值到当前为止的消费，但要考虑回收问题，如果回收后再次充值，则此后的消费计算在下次充值后的消费统计中
2、同一张卡片前后几次充值的金额可以不同
3、卡片的面额等于第一次的充值金额，以后再充值，面额不会变
------解决方案--------------------------------------------------------
- SQL code
SQL> SELECT * FROM cz; CARDNO TIMES LODDATE LODAMT-------- ---------- ----------- ----------91034567 1 2010-2-12 10091034567 2 2010-5-21 10091034567 3 2010-9-16 20091045678 1 2010-3-1 20091045678 2 2010-3-23 30091056789 1 2010-2-19 300 6 rows selectedSQL> SELECT * FROM xf; CARDNO BLANCE AMT PAYDATE-------- ---------- ---------- -----------91034567 100 40 2010-2-1591034567 100 60 2010-3-2691034567 100 100 2010-8-1191034567 100 160 2010-10-391045678 200 120 2010-3-191045678 200 80 2010-3-591045678 200 220 2010-5-1591056789 300 278 2010-5-2 8 rows selectedSQL> SELECT to_char(loddate, 'yyyymm') loddate, SUM(lodamt) lodall, SUM(amt) amtall 2 FROM (SELECT c.*, 3 (SELECT SUM(amt) 4 FROM xf x 5 WHERE c.cardno = x.cardno 6 AND x.paydate BETWEEN c.loddate AND c.lloddate) amt 7 FROM (SELECT cz.*, 8 lead(loddate, 1, DATE '3000-01-01') 9 over (PARTITION BY cardno ORDER BY loddate) - 1 lloddate 10 FROM cz) c) 11 GROUP BY to_char(loddate, 'yyyymm'); LODDATE LODALL AMTALL------- ---------- ----------201002 400 378201003 500 420201005 100 100201009 200 160 SQL>
------解决方案--------------------------------------------------------
- SQL code
SELECT TO_CHAR(A.LODDATE,'YYYYMM') LODDATE,MAX(A.SAMT) AS LODAMT,SUM(B.AMT)FROM (SELECT CZ.*,SUM(LODAMT) OVER(PARTITION BY TO_CHAR(LODDATE,'YYYYMM')) SAMTFROM CZ) A,XF BWHERE A.CARDNO = B.CARDNOAND A.LODDATE <= B.PAYDATEAND NOT EXISTS(SELECT 1 FROM CZ CWHERE C.CARDNO= A.CARDNO AND C.LODDATE <= B.PAYDATEAND C.LODDATE > A.LODDATE)GROUP BY TO_CHAR(A.LODDATE,'YYYYMM')
------解决方案--------------------------------------------------------
一个SQL搞定不一定是最好的，
如果使用SQL，关键要看你的SQL调优情况！！
------解决方案--------------------------------------------------------
你的需求复杂，用一条sql搞定，效率不一定高，考虑一下存储过程实现吧
------解决方案--------------------------------------------------------
-- 脑子中不要有这个概念：复杂的问题一个SQL搞定，这就是牛皮啦......!
-- 关键得看效率！如果一个SQL搞定啦，但是老板为了看到结果，等待了12个小时，