查询将给出两个日期范围之间的日期列表 [英] Query which will give a list of dates between two date ranges

问题描述

我的问题类似于以下问题:

My question is similar to following question:

http://asktom .oracle.com/pls/asktom/f?p = 100:11:0 ::::: P11_QUESTION_ID:14582643282111

区别在于我的内部查询返回两条记录，而我有外部查询.

The difference is my inner query returns two records and I have outer query.

我需要编写类似这样的内部查询，这将为我提供两个日期范围之间的日期列表(我正在尝试不执行此查询).

I need to write inner query something like this which will give me a list of dates between two date ranges (I am trying this query whcih does not execute).

Select * from outerTable where  my_date in
(   
    select to_date(r.REQ_DATE) + rownum -1
     from all_objects, (MY_INNER_QUERY Where ID =100) r 
     where rownum <= to_date(r.DUE_DATE,'dd-mon-yyyy')-to_date(r.REQ_DATE,'dd-mon-yyyy')+1;
)

我的内部查询返回以下2行:

My inner query returns following 2 rows:

Select * from innerTable Where ID =100


    Start date           end date 
    3/19/2013            3/21/2013 
    3/8/2013             3/8/2013

所以我需要内部查询，该查询将以下日期返回到外部查询:

So I am need inner query which will return following dates to outer query:

    3/19/2013
    3/20/2013
    3/21/2013
    3/8/2013

推荐答案

好问题-这真的吸引了我！答案或多或少地埋在汤姆的职位上.这是简短的版本，使用称为TestDR的表来定义范围.首先是TestDR的内容:

Great question - this one really drew me in! The answer was more or less buried further down on Tom's post. Here's the short version, using a table called TestDR to define the ranges. First the TestDR contents:

SELECT * FROM TestDR;

STARTDATE ENDDATE
--------- ---------
19-MAR-13 21-MAR-13
08-MAR-13 08-MAR-13

现在查询可为范围内的每个日期创建一行:

Now for the query to create one row for each date in the range:

WITH NUMS AS (
  SELECT LEVEL-1 DaysToAdd
  FROM DUAL
  CONNECT BY LEVEL <= 60
)
SELECT StartDate + DaysToAdd TheDate
FROM TestDR
CROSS JOIN NUMS
WHERE TestDR.EndDate - TestDR.StartDate + 1 > DaysToAdd
ORDER BY 1

THEDATE
---------
08-MAR-13
19-MAR-13
20-MAR-13
21-MAR-13

根据汤姆的帖子改编的查询，您必须知道种子" NUMS查询的最大范围.他在示例中使用了60，所以这就是我上面所使用的.如果您认为子查询中的任何行都不会超过60天，则可以解决问题.如果您认为最大值可能长达1000天(大约三年)，则将60天更改为1000天.我尝试了一下，并查询了2 1/2年的范围，结果是即时的.

With the query adapted from Tom's posting, you have to know the maximum range going in to "seed" the NUMS query. He used 60 in his example so that's what I used above. If you don't think any row from your subquery will ever have a range of more than 60 days then this will do the trick. If you think the maximum could be as much as 1000 days (about three years) then change the 60 to 1000. I tried this and queried a 2 1/2 year range and the results were instantaneous.

如果您想指定确切的种子"计数，并且愿意让查询稍微复杂一点，则可以计算它.这是我可以使用TestDR表完成的方法:

If you want to specify the exact "seed" count you can calculate it if you're willing to make the query a bit more complicated. Here's how I can do it with my TestDR table:

WITH NUMS AS (
  SELECT LEVEL-1 DaysToAdd
  FROM DUAL
  CONNECT BY LEVEL <= (
    SELECT MAX(EndDate - StartDate + 1)
    FROM TestDR)
)
SELECT ... and the rest of the query as above

这篇关于查询将给出两个日期范围之间的日期列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！