为日期范围内的每个月生成单独的行 [英] Generating separate rows for each month in a date range
问题描述
基本上我有一行数据,例如:
Basically I have one row of data, for example:
ID - Start_Date - End_Date
------------------------------------
XXA 1/23/14 3/12/14
我想为 Start_Date
和 End_Date
之间的每个月创建一行,为每个 ID
,例如:
And I want to create one row for each month between the Start_Date
and End_Date
, for each ID
, for example:
ID - Month - Year
--------------------------
XXA January 2014
XXA February 2014
XXA March 2014
执行此操作的最佳和最有效方法是什么?我正在考虑使用游标,但是一旦创建了这个表,我就需要将其他的加入到这个表中.我是 oracle 的新手,我不确定在运行游标并创建临时表后是否可以加入其他表.任何帮助将不胜感激.
Which is the best and most efficient way of doing this? I was thinking of using a cursor, but once this table is created, I need to join other ones to this one. I am new to oracle, and I am not sure if you can join other tables after you run a cursor and create a temporary table. Any help would be greatly appreciated.
推荐答案
您可以通过使用 CONNECT BY
语法来使用简单的行生成技术:
You can use a simple row generation technique by using the CONNECT BY
syntax:
with sample_data as
(select 'XXA' id, to_date('1/23/14','MM/DD/RR') start_date, to_date('3/12/14','MM/DD/RR') end_date from dual)
select id, to_char(add_months(start_date,level - 1),'Month YYYY') date_column
from sample_data
connect by level <= extract(month from end_date) - extract(month from start_date) + 1;
编辑添加 DISTINCT
应该允许它跨行工作,我相信,尽管我有兴趣被证明是错误的.编辑 2 修改示例以处理多年(最初应该这样做).(参见示例 http://sqlfiddle.com/#!4/9eecb/4097/0.)
Edit
Adding DISTINCT
should allow this to work across rows, I believe, although I would be interested to be proven wrong.
Edit 2 Modified example to handle multiple years (should have done that originally). (See example http://sqlfiddle.com/#!4/9eecb/4097/0.)
with sample_data as
( select 'XXA' id, to_date('1/23/14','MM/DD/RR') start_date, to_date('3/12/15','MM/DD/RR') end_date from dual union all
select 'XXB' id, to_date('4/12/14','MM/DD/RR') start_date, to_date('6/18/15','MM/DD/RR') end_date from dual )
select distinct
id,
to_char(add_months(start_date,level - 1),'Month YYYY') date_column,
add_months(start_date,level -1) sortkey
from sample_data
connect by level <= ceil(months_between(trunc(end_date,'MM'), trunc(start_date,'MM'))) + 1
order by id, sortkey;
在我的沙箱数据库中,这会产生:
In my sandbox DB this yields:
ID DATE_COLUMN SORT_COL
XXA January 2014 23-JAN-2014 00:00:00
XXA February 2014 23-FEB-2014 00:00:00
XXA March 2014 23-MAR-2014 00:00:00
XXB April 2014 12-APR-2014 00:00:00
XXB May 2014 12-MAY-2014 00:00:00
XXB June 2014 12-JUN-2014 00:00:00
这篇关于为日期范围内的每个月生成单独的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!