需要显示一个虚拟月份 – Oracle SQL

概述对于月费报告目的,需要显示如下所述的输出.只有当月支付费用时才会在表格中输入数据,否则表格中不会显示该月份. 执行查询 select reg_id, dop,to_char(dop, 'FMMonth,YYYY') Month, status from fees_status where dop between ('1-Jul-2012') and ('1-Dec-2012') 输出是 R 对于月费报告目的,需要显示如下所述的输出.只有当月支付费用时才会在表格中输入数据,否则表格中不会显示该月份.

执行查询

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           STATUSAE-8    7/1/2012    July,2012       PAIDAE-8    9/1/2012    September,2012  PAIDAE-8    11/1/2012   November,2012   PAIDAE-8    12/1/2012   December,2012   PAID

对于报告目的,需要输出为

REG_ID  DOP         MONTH           STATUSAE-8    7/1/2012    July,2012       PAIDAE-8    8/1/2012    August,2012     NOTPAIDAE-8    9/1/2012    September,2012  PAIDAE-8    10/1/2012   October,2012    NOTPAIDAE-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            PAIDAE-8   01-Aug-12   August,2012          NOTPAIDAE-8   01-Sep-12   September,2012       PAIDAE-8   01-Oct-12   October,2012         NOTPAIDAE-8   01-Nov-12   November,2012        PAIDAE-8   01-Dec-12   December,2012        PAID6 rows selected

总结

以上是内存溢出为你收集整理的需要显示一个虚拟月份 – Oracle SQL全部内容，希望文章能够帮你解决需要显示一个虚拟月份 – Oracle SQL所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。