table:
mytalbe
columns:
month -- varchar2(20)
myvalue -- varchar2(20)
recodes:
month myvalue
1 123
2 43
7 1231
我希望的结果:
month myvalue
1 123
2 43
3
4
5
6
7 1231
8
9
10
11
12
说明,数据库中,每个月都只有一条记录,不用考虑某个月对应多条记录的情况。
mytalbe
columns:
month -- varchar2(20)
myvalue -- varchar2(20)
recodes:
month myvalue
1 123
2 43
7 1231
我希望的结果:
month myvalue
1 123
2 43
3
4
5
6
7 1231
8
9
10
11
12
说明,数据库中,每个月都只有一条记录,不用考虑某个月对应多条记录的情况。
FROM mytable
RIGHT JOIN (SELECT rownum rn FROM dual CONNECT BY rownum < 13) ON rn = MONTH试试这个
FROM mytable
RIGHT JOIN (SELECT rownum rn FROM dual CONNECT BY rownum < 12) ON rn = MONTH order by rn楼上高人
用rownum来生成月份就行了
比如SELECT TO_CHAR (ADD_MONTHS (ADD_MONTHS (TRUNC (:base_day, 'mm'),
-:tot_months
),
ROWNUM - 1
),
'yyyy-mm'
) months
FROM DUAL
CONNECT BY ROWNUM <=
MONTHS_BETWEEN (:base_day,
ADD_MONTHS (TRUNC (:base_day, 'mm'),
-:tot_months
)
)
+ 1这是我生成月份的语句,根据基准日,预计统计月数,生成月份
然后其它表和这个表关联,用外联,就行了
(select to_char(add_months(sysdate,-level+1),'mm') month
from dual connect by level <=12 order by month) a left join mytalbe b on a.month=b.month;不知道有没有错,可以先试下