oracle 如何显示月份,需要显示一个虚拟月份 – Oracle SQL

您可以使用外部联接的

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', 'MM/DD/YYYY'), 'September,2012','PAID' from dual union all

4 select 'AE-8', to_date('11/1/2012', 'MM/DD/YYYY'),'November,2012' ,'PAID' from dual union all

5 select 'AE-8', to_date('12/1/2012', 'MM/DD/YYYY'),'December,2012' ,'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