365天创建视图 [英] Create View with 365 days
本文介绍了365天创建视图的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何使用一年中的所有天数创建View
. view
应该用从JAN-01到Dec-31的日期填充.我如何在Oracle中做到这一点?
How to Create a View
with all days in year. view
should fill with dates from JAN-01 to Dec-31. How can I do this in Oracle ?
如果当前年份有365天,则view
应该有365行带日期.如果当前年份有366天,则view
应该有366行带日期.我希望view
具有类型为DATE
的单列.
If current year have 365 days,view
should have 365 rows with dates. if current year have 366 days,view
should have 366 rows with dates. I want the view
to have a single column of type DATE
.
推荐答案
这个简单的视图可以做到:
This simple view will do it:
create or replace view year_days as
select trunc(sysdate, 'YYYY') + (level-1) as the_day
from dual
connect by level <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'))
/
喜欢这个:
SQL> select * from year_days;
THE_DAY
---------
01-JAN-11
02-JAN-11
03-JAN-11
04-JAN-11
05-JAN-11
06-JAN-11
07-JAN-11
08-JAN-11
09-JAN-11
10-JAN-11
11-JAN-11
...
20-DEC-11
21-DEC-11
22-DEC-11
23-DEC-11
24-DEC-11
25-DEC-11
26-DEC-11
27-DEC-11
28-DEC-11
29-DEC-11
30-DEC-11
31-DEC-11
365 rows selected.
SQL>
日期是通过应用几个Oracle日期函数生成的:
The date is generated by applying several Oracle date functions:
-
trunc(sysdate, 'yyyy')
为我们提供了本年度的1月1日 -
add_months(x, 11)
给我们十二月的第一天 -
last_day(x)
给了我们12月31日 -
to_char(x, 'DDD')
给出了12月31日的数字,今年是365,明年是366. - 最后一个图提供了行生成器
CONNECT BY LEVEL <= X
的上限
trunc(sysdate, 'yyyy')
gives us the first of January for the current yearadd_months(x, 11)
gives us the first of Decemberlast_day(x)
gives us the thirty-first of Decemberto_char(x, 'DDD')
gives us the number of the thirty-first of December, 365 this year and 366 next.- This last figure provides the upper bound for the row generator
CONNECT BY LEVEL <= X
这篇关于365天创建视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文