SQL 使用oracle sql列出所有月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4582861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List all the months using oracle sql
提问by Dead Programmer
Guys, is there any better way to list all the months other than this:
伙计们，有没有更好的方法来列出除此之外的所有月份：
select to_char(add_months(to_date('01/01/1000', 'DD/MM/RRRR'), ind.l-1), 'MONTH') as month_descr,
ind.l as month_ind
from dual descr,
(select l
from (select level l
from dual
connect by level <= 12
)
) ind
order by 2;
ANSWER:
回答：
SELECT to_char(add_months(SYSDATE, (LEVEL-1 )),'MONTH') as months
FROM dual
CONNECT BY LEVEL <= 1
ONE MORE QUESTION SEE BELOW
还有一个问题见下文
Also I want to list the previous two years including the current year. I wrote this sql query. Let me know if there is anything better.
另外我想列出前两年，包括今年。我写了这个 sql 查询。让我知道是否有更好的东西。
select extract(year from sysdate) - (level-1) as years
from dual
connect by level <=3
order by years
回答by Rob van Wijk
Not better, but just a bit cleaner:
不是更好，而是更干净一点：
SQL> select to_char(date '2000-12-01' + numtoyminterval(level,'month'),'MONTH') as month
2 from dual
3 connect by level <= 12
4 /
MONTH
---------
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER
12 rows selected.
Regards, Rob.
问候，罗布。
回答by ksogor
Yup.
是的。
1:
1：
SELECT * FROM WWV_FLOW_MONTHS_MONTH;
2: (UPD:)
2：（UPD:)
WITH MONTH_COUNTER AS (
SELECT LEVEL-1 AS ID
FROM DUAL
CONNECT BY LEVEL <= 12
)
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/01/1000', 'DD/MM/RRRR'), ID),'MONTH') FROM MONTH_COUNTER;
回答by Doug Porter
select to_char(add_months(trunc(sysdate, 'yyyy'), level - 1), 'MONTH') months
from dual
connect by level <= 12;
Returns:
返回：
MONTHS
--------------------
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER
12 rows selected.
回答by Prasant
SELECT to_char(to_date( level,'mm'), 'MONTH') Months FROM DUAL CONNECT BY LEVEL <=12;
Regards, Prasant Sutaria
问候， Prasant Sutaria
回答by Sandeep Mandloi
SELECT TO_CHAR(TO_DATE(rownum||'-'||rownum||'-'||'2013', 'DD-MM-YYYY'), 'Month')
FROM all_objects
WHERE rownum < 13
