对于月费报告目的,需要显示如下所述的输出.只有当月支付费用时才会在表格中输入数据,否则表格中不会显示该月份.
执行查询
select reg_id,dop,to_char(dop,'FMMonth,YYYY') Month,status
from fees_status
where dop between ('1-Jul-2012') and ('1-Dec-2012')
输出是
REG_ID DOP MONTH STATUS AE-8 7/1/2012 July,2012 PAID AE-8 9/1/2012 September,2012 PAID AE-8 11/1/2012 November,2012 PAID AE-8 12/1/2012 December,2012 PAID
对于报告目的,需要输出为
REG_ID DOP MONTH STATUS AE-8 7/1/2012 July,2012 PAID AE-8 8/1/2012 August,2012 NOTPAID AE-8 9/1/2012 September,2012 PAID AE-8 10/1/2012 October,2012 NOTPAID AE-8 11/1/2012 November,2012 PAID
解决方法
您可以使用外部联接的
partition by扩展来实现此目的.这是一个例子：
-- sample of data from your question sql> with fees_status(REG_ID,DOP,MONTH1,STATUS) as( 2 select 'AE-8',to_date('7/1/2012','MM/DD/YYYY'),'July,2012','PAID' from dual union all 3 select 'AE-8',to_date('9/1/2012','September,'PAID' from dual union all 4 select 'AE-8',to_date('11/1/2012','November,'PAID' from dual union all 5 select 'AE-8',to_date('12/1/2012','December,'PAID' from dual 6 ),-- dates 7 dates(dt) as( 8 select add_months(to_date('01/01/2012','mm/dd/yyyy'),level - 1) 9 from dual 10 connect by level <= 12 11 ) 12 select t.reg_id 13,d.dt as dop 14,to_char(d.dt,'fmMonth,YYYY') as month1 15,Nvl(t.status,'NOTPAID') as status 16 from fees_status t 17 partition by (t.reg_id) 18 right outer join dates d 19 on (d.dt = t.dop) 20 where d.dt between (to_date('1-Jul-2012','dd-Month-yyyy')) 21 and (to_date('1-Dec-2012','dd-Month-yyyy')) 22 ; REG_ID DOP MONTH1 STATUS ------ ----------- -------------------- ------- AE-8 01-Jul-12 July,2012 PAID AE-8 01-Aug-12 August,2012 NOTPAID AE-8 01-Sep-12 September,2012 PAID AE-8 01-Oct-12 October,2012 NOTPAID AE-8 01-Nov-12 November,2012 PAID AE-8 01-Dec-12 December,2012 PAID 6 rows selected
