如何在 PL/SQL 中迭代日期范围_数据库问题-跟版网

本文介绍了如何在 PL/SQL 中迭代日期范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

我需要编写一份报告，根据每个记录的日期范围生成汇总表.

I need to write a report that generates summary totals against a table with date ranges for each record.

table data:
option   start_date   end_date
opt1     6/12/2009    6/19/2009
opt1     6/3/2009     6/13/2009
opt2     6/5/2009     6/6/2009

我想要的基本上是这样的:

What I want out is basically this:

date       option    count
6/1/2009   opt1      0
6/1/2009   opt2      0
6/2/2009   opt1      0
6/2/2009   opt2      0
6/3/2009   opt1      0
6/3/2009   opt2      1

我很难弄清楚如何迭代一个日期范围.我确信这是一些可以为此创建的简单游标，但我不知所措.最好在 PL/SQL 中

I am having a hard time figuring out how to iterate over a date range. I am sure this is some simple cursor that could be created for this but I am at a loss. Preferably in PL/SQL

更新:

我最终使用了此处的示例来完成我想要的去做.这将创建一个生成日期表的函数.

I ended up using the example here to accomplish what I wanted to do. This creates a function that generates a table of dates.

推荐答案

您将需要某种日历来循环遍历日期范围.我已经使用按级别连接技巧.然后，您可以将您的数据加入日历(交叉加入，因为即使当天没有选项，您也需要一行):

You will need some sort of calendar to loop through a range of date. I have built one using the connect by level trick. You can then join the calendar with your data (cross join since you want a row even when there is no option for that day):

SQL> WITH calendar AS (
  2     SELECT to_date(:begin_date, 'mm/dd/yyyy') + ROWNUM - 1 c_date
  3       FROM dual
  4      CONNECT BY LEVEL <= to_date(:end_date, 'mm/dd/yyyy') 
                             - to_date(:begin_date, 'mm/dd/yyyy') + 1
  5  )
  6  SELECT c_date "date", d_option "option", COUNT(one_day)
  7    FROM (SELECT c.c_date, d.d_option,
  8                  CASE
  9                     WHEN c.c_date BETWEEN d.start_date AND d.end_date THEN
 10                      1
 11                  END one_day
 12             FROM DATA d, calendar c)
 13   GROUP BY c_date, d_option
 14  ORDER BY 1,2;

date        option COUNT(ONE_DAY)
----------- ------ --------------
01/06/2009  opt1                0
01/06/2009  opt2                0
02/06/2009  opt1                0
02/06/2009  opt2                0
03/06/2009  opt1                1
03/06/2009  opt2                0
04/06/2009  opt1                1
04/06/2009  opt2                0
05/06/2009  opt1                1
05/06/2009  opt2                1
06/06/2009  opt1                1
06/06/2009  opt2                1

12 rows selected

这篇关于如何在 PL/SQL 中迭代日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！

如何在 PL/SQL 中迭代日期范围

问题描述

推荐答案

相关文档推荐