sql - 使用两点之间的一组日期(星期日)进行子查询

Question

我需要形成几个包含日期集的子查询，仅包括两个设置点之间的星期日，例如 01.04.2018 和 30.06.2018 之间。

想到的第一件事是这样的:

SELECT '01.04.2018' STARTDATE FROM DUAL
UNION ALL
SELECT '08.04.2018' STARTDATE FROM DUAL
...

但是看起来不是很优雅。有没有更简单的方法来实现我的需要？

Answer 1

您可以使用:

SQL Fiddle

查询 1 :

SELECT NEXT_DAY( DATE '2018-04-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7
         AS startdate
FROM   DUAL
CONNECT BY
       NEXT_DAY( DATE '2018-04-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7
         <= DATE '2018-06-30'

Results :

|            STARTDATE |
|----------------------|
| 2018-04-01T00:00:00Z |
| 2018-04-08T00:00:00Z |
| 2018-04-15T00:00:00Z |
| 2018-04-22T00:00:00Z |
| 2018-04-29T00:00:00Z |
| 2018-05-06T00:00:00Z |
| 2018-05-13T00:00:00Z |
| 2018-05-20T00:00:00Z |
| 2018-05-27T00:00:00Z |
| 2018-06-03T00:00:00Z |
| 2018-06-10T00:00:00Z |
| 2018-06-17T00:00:00Z |
| 2018-06-24T00:00:00Z |

Does this construction always return at least one row? If I add an additional condition that NEXT_DAY( DATE '2018-07-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7 should be less than SYSDATE it still returns the first row which is larger than SYSDATE.

是的，如果过滤只是在 CONNECT BY 中执行，分层查询将始终返回一行。子句(因为它只会在尝试将一行连接到其父级时才会检查它，并且需要首先生成至少一个父级才能执行此操作):

SELECT NEXT_DAY( DATE '2018-07-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7
         AS startdate
FROM   DUAL
CONNECT BY
       NEXT_DAY( DATE '2018-07-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7
         <= LEAST(
              SYSDATE, -- DATE '2018-06-29'
              DATE '2018-07-30'
            )

结果 :

|            STARTDATE |
|----------------------|
| 2018-07-01T00:00:00Z | -- Greater than SYSDATE

但是如果你添加一个 WHERE子句(而不是在 CONNECT BY 子句中过滤)然后它可以返回零行:

SELECT NEXT_DAY( DATE '2018-07-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7
         AS startdate
FROM   DUAL
WHERE  NEXT_DAY( DATE '2018-07-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7
         <= SYSDATE
CONNECT BY
       NEXT_DAY( DATE '2018-07-01' - 1, 'SUNDAY' ) + ( LEVEL - 1 ) * 7
         <= DATE '2018-07-30'

结果 :

No data found.