时间戳

首页 > 解决方案 > 带有间隔的日期的Oracle递归CTE

sql - 带有间隔的日期的Oracle递归CTE

问题描述

我正在尝试创建一个递归 CTE，它每 10 分钟生成一个日期/时间，并在午夜停止，但我在语法上苦苦挣扎，似乎无法让它工作。

我会非常感谢任何可以帮助我的人。提前感谢所有回答的人。


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';


with date_rows 
(( SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS') as start_date  FROM DUAL ) 
union all
select start_date+interval '10' minute
 from date_rows
where start_date < 
TRUNC(start_date) + INTERVAL '1' DAY)
select * from date_rows;

标签： sqloracledatetimerecursioncommon-table-expression

解决方案

您可以使用：

WITH inputs ( value ) AS (
  SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')
  FROM   DUAL
),
date_rows ( start_date, end_date ) AS (
  SELECT value,
         TRUNC(value) + INTERVAL '1' DAY
  FROM   inputs
UNION ALL
  SELECT start_date + INTERVAL '10' MINUTE,
         end_date
  FROM   date_rows
  WHERE  start_date + INTERVAL '10' MINUTE < end_date
)
SELECT start_date
FROM   date_rows;

或者，如果要复制输入值，则可以使用：

WITH date_rows ( start_date ) AS (
  SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')
  FROM   DUAL
UNION ALL
  SELECT start_date + INTERVAL '10' MINUTE
  FROM   date_rows
  WHERE  start_date + INTERVAL '10' MINUTE
           < TRUNC(TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS'))
             + INTERVAL '1' DAY
)
SELECT *
FROM   date_rows;

注意：在递归子查询的每次迭代中，start_date是前一个值，因此您需要检查是否start_date + INTERVAL '10' MINUTE在午夜之前（而不仅仅是start_date），否则您将在午夜之后获得最后一行。

db<>在这里摆弄

你的代码有什么问题：

  1. 对于非递归子查询因式分解子句，语法为：

    WITH query_alias AS (

    或者

    WITH query_alias (column_alias) AS (

    你错过了AS关键字。

    但是，对于递归子查询因式分解子句，您需要使用第二个版本并指定列别名。

  2. 第()一个括号SELECT是多余的（但不会导致错误）。

  3. start_date < TRUNC(start_date) + INTERVAL '1' DAY)总是正确的，即使日期超过了午夜的界限；所以查询将无限递归。

db<>在这里摆弄

推荐阅读