连接几个月 [英] connected by months
问题描述
连接由
的东西。但它总是非常有用的。我有这个小问题,你们可能可以帮助我... 给定开始月份(说 to_char(sysdate,'YYYYMM')
)和结束月份(例如 to_char(add_months(sysdate,6),'YYYYMM')
),想要获取月份列表之间,以相同的格式。
嗯,我想使用这个分区自动化脚本。我最好的镜头到目前为止(很可怜)产生无效的月份,例如'201034'...(是的,我知道,令人难以置信的低效率)
遵循以下代码:从
(选择to_char(add_months(sysdate,6),'YYYYMM')作为tn_end,选择id
,to_char(sysdate,'YYYYMM')作为tn_start从双)tabla,
(select * from
(从Level双层连接中选择级别为Id)=(选择to_char(add_months(sysdate,1 ),YYYYMM)from))其中id> to_char(sysdate,'YYYYMM'))t
其中
t.Id在tabla.tn_start和tabla.tn_end之间
如何使此查询只返回有效月份?任何提示?
欢呼伙伴
f。
最好的方法是将行生成器与date函数分开。因此,从0到6生成一个列表,并从中计算出几个月。如果你想通过几个月,那么在with子句
中以my_counter作为(
选择Level- 1作为id
从双
连接级别< = 7
)
从my_counter
中选择to_char(add_months(sysdate,id),'YYYYMM' / code>
下面的示例将允许您插入您需要的日期来解决差异。
with my_counter as(
从Level
中选择Level $ 1
连接级别< = months_between(add_months (trunc(sysdate,'MM'),6),
trunc(sysdate,'MM'))+ 1
)
select to_char(add_months(trunc(sysdate,'MM' ,id),'YYYYMM')from my_counter
Ok, I'm new using this connect by
thing. But its always quite useful. I have this small problem you guys might be able to help me...
Given start month (say to_char(sysdate,'YYYYMM')
) and end month (say, to_char(add_months(sysdate, 6),'YYYYMM')
), want to get the list of months in between, in the same format.
Well, I want to use this into a partitions automation script. My best shot so far (pretty pitiful) yields invalid months e.g.'201034'... (and yea, I know, incredibly inefficient)
Follows the code:
SELECT id
from
(select to_char(add_months(sysdate, 6),'YYYYMM') as tn_end, to_char(sysdate,'YYYYMM') as tn_start from dual) tabla,
(select * from
(Select Level as Id from dual connect by Level <= (Select to_char(add_months(sysdate, 1),'YYYYMM')from dual)) where id > to_char(sysdate,'YYYYMM')) t
Where
t.Id between tabla.tn_start and tabla.tn_end
how do I do to make this query return only valid months? Any tips?
cheers mates,
f.
Best way might be to separate out the row generator from the date function. So generate a list from 0 to 6 and calculate months from that. If you want to pass the months in then do that in the with clause
with my_counter as (
Select Level-1 as id
from dual
connect by Level <= 7
)
select to_char(add_months(sysdate, id),'YYYYMM') from my_counter
The example below will allow you to plug in the dates you require to work out the difference.
with my_counter as (
Select Level-1 as id
from dual
connect by level <= months_between(add_months(trunc(sysdate,'MM'), 6),
trunc(sysdate,'MM')) + 1
)
select to_char(add_months(trunc(sysdate, 'MM'), id),'YYYYMM') from my_counter
这篇关于连接几个月的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!