如何使用Oracle SQL查询生成给定日期和间隔的日期行？

ssm49v7z 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(123)

Oracle DB 18c：对于JOBID、JOBSPEC、TRIGDT、GAPDAYS等列，我想根据TRIGDT和GAPDAYS再生成两个日期。

显示生成的日期的新列将是PRINDT和COPYDT。
对于第一行，PRINDT =TRIGDT，COPYDT= PRINDT +GAPDAYS-1。
对于下一行，PRINDT =（来自前一行的COPYDT）+1和COPYDT=（新PRINDT）+GAPDAYS-1。
行将继续生成，直到COPYDT不能大于SYSDATE（当前日期）
对于输入中的每个唯一行，JOBID、JOBSPEC、TRIGDT、GAPDAYS在所有重复行中保持不变

我可以在PLSQL中使用条件循环来实现这一点，但是如果我可以使用查询来实现这一点，那就太好了。如何使用Oracle SQL查询实现这一点？可能是一个“禁止”条款，但我不确定我如何处理它。请求您的帮助。
输入数据的所需输出如下所示（SYSDATE=9/24/2023）：

输入数据：

WITH
    tbl_print_schd
    AS
        (SELECT 'J101' AS JOBID, 'PRINT_DOC164' AS JOBSPEC, TO_DATE ( '06/29/2023', 'MM/DD/YYYY' ) AS TRIGDT, 15 AS GAPDAYS FROM SYS.DUAL
         UNION
         SELECT 'J2213' AS JOBID, 'PRINT_SYS_TBL22' AS JOBSPEC, TO_DATE ( '8/26/2023', 'MM/DD/YYYY' ) AS TRIGDT, 9 AS GAPDAYS FROM SYS.DUAL
         UNION
         SELECT 'J66' AS JOBID, 'ILLUM_STIG93' AS JOBSPEC, TO_DATE ( '9/10/2023', 'MM/DD/YYYY' ) AS TRIGDT, 11 AS GAPDAYS FROM SYS.DUAL)
SELECT JOBID
     , JOBSPEC
     , TRIGDT
     , GAPDAYS
  FROM tbl_print_schd;

**编辑1（2023年9月25日）：**添加了我之前错过的另一个要求：

如果TRIGDT为NULL，则不应生成任何行。输出不应显示此行。
如果包含基于TRIGDT和GAPDAYS的COPYDT的第一行大于SYSDATE，则也应从输出中省略该行。

oracle

来源：https://stackoverflow.com/questions/77167006/how-do-i-generate-rows-of-dates-given-a-date-and-interval-using-oracle-sql-query

2条答案

按热度按时间

l7wslrjt1#

这是一个 * 行生成器 *，正如你所说的。这里有一个选项，它显示了如何做到这一点。如果你觉得应该的话，可以调整一下。
样本数据：

SQL> WITH
  2      tbl_print_schd
  3      AS
  4          (SELECT 'J101' AS JOBID, 'PRINT_DOC164' AS JOBSPEC, TO_DATE ( '06/29/2023', 'MM/DD/YYYY' ) AS TRIGDT, 15 AS GAPDAYS FROM SYS.DUAL
  5           UNION
  6           SELECT 'J2213' AS JOBID, 'PRINT_SYS_TBL22' AS JOBSPEC, TO_DATE ( '8/26/2023', 'MM/DD/YYYY' ) AS TRIGDT, 9 AS GAPDAYS FROM SYS.DUAL
  7           UNION
  8           SELECT 'J66' AS JOBID, 'ILLUM_STIG93' AS JOBSPEC, TO_DATE ( '9/10/2023', 'MM/DD/YYYY' ) AS TRIGDT, 11 AS GAPDAYS FROM SYS.DUAL)

查询方式：

9  SELECT JOBID
 10       , JOBSPEC
 11       , TRIGDT
 12       , GAPDAYS,
 13       --
 14       trigdt + (gapdays * (column_value - 1))  as printdt,
 15       trigdt + (gapdays * (column_value - 1)) + gapdays - 1 as copydt
 16    FROM tbl_print_schd cross join
 17    table(cast(multiset(select level from dual
 18                        connect by level <= (sysdate - trigdt) / gapdays
 19                      ) as sys.odcinumberlist));

JOBID JOBSPEC         TRIGDT        GAPDAYS PRINTDT    COPYDT
----- --------------- ---------- ---------- ---------- ----------
J101  PRINT_DOC164    06/29/2023         15 06/29/2023 07/13/2023
J101  PRINT_DOC164    06/29/2023         15 07/14/2023 07/28/2023
J101  PRINT_DOC164    06/29/2023         15 07/29/2023 08/12/2023
J101  PRINT_DOC164    06/29/2023         15 08/13/2023 08/27/2023
J101  PRINT_DOC164    06/29/2023         15 08/28/2023 09/11/2023
J2213 PRINT_SYS_TBL22 08/26/2023          9 08/26/2023 09/03/2023
J2213 PRINT_SYS_TBL22 08/26/2023          9 09/04/2023 09/12/2023
J2213 PRINT_SYS_TBL22 08/26/2023          9 09/13/2023 09/21/2023
J66   ILLUM_STIG93    09/10/2023         11 09/10/2023 09/20/2023

9 rows selected.

SQL>

赞(0）回复(0）举报 2023-10-16

ovfsdjhp2#

可以使用递归子查询：

WITH rsq (jobid, jobspec, trigdt, gapdays, printdt, copydt) AS (
  SELECT jobid, jobspec, trigdt, gapdays, trigdt, trigdt + gapdays - 1
  FROM   tbl_print_schd
  WHERE  trigdt + gapdays - 1 <= SYSDATE
UNION ALL
  SELECT jobid, jobspec, trigdt, gapdays, printdt + gapdays, copydt + gapdays
  FROM   rsq
  WHERE  copydt + gapdays <= SYSDATE
)
SEARCH DEPTH FIRST BY jobid SET orderid
SELECT jobid, jobspec, trigdt, gapdays, printdt, copydt
FROM   rsq;

其中，对于样本数据：

CREATE TABLE tbl_print_schd (jobid, jobspec, trigdt, gapdays) AS
  SELECT 'J101',  'PRINT_DOC164',    DATE '2023-06-29', 15 FROM DUAL UNION ALL
  SELECT 'J2213', 'PRINT_SYS_TBL22', DATE '2023-08-26',  9 FROM DUAL UNION ALL
  SELECT 'J66',   'ILLUM_STIG93',    DATE '2023-09-10', 11 FROM DUAL UNION ALL
  SELECT 'J123',  'XYZ',             NULL,              99 FROM DUAL UNION ALL
  SELECT 'J456',  'ABC',             TRUNC(SYSDATE) - 9, 11 FROM DUAL UNION ALL
  SELECT 'J789',  'DEF',             TRUNC(SYSDATE) - 9, 10 FROM DUAL;

输出：
| JobID|公司简介|TRIGDT| GAPDAYS| PRINDT| COPYDT|
| --|--|--|--|--|--|
| J101|打印_DOC 164| 2023年6月29日00：00：00| 15 |2023年6月29日00：00：00| 2023年7月13日00：00：00|
| J101|打印_DOC 164| 2023年6月29日00：00：00| 15 |2023-07-14 00：00：00| 2019 -07-28 00：00：00|
| J101|打印_DOC 164| 2023年6月29日00：00：00| 15 |2023-07-29 00：00：00| 2023-08-12 00：00：00|
| J101|打印_DOC 164| 2023年6月29日00：00：00| 15 |2023-08-13 00：00：00| 2023-08-27 00：00：00|
| J101|打印_DOC 164| 2023年6月29日00：00：00| 15 |2019 -08-28 00：00：00| 2023-09-11 00：00：00|
| J2213| PRINT_SYS_TBL 22| 2019 -08-26 00：00：00| 9 |2019 -08-26 00：00：00| 2019 -09-03 00：00：00|
| J2213| PRINT_SYS_TBL 22| 2019 -08-26 00：00：00| 9 |2019 -09-04 00：00：00| 2023-09-12 00：00：00|
| J2213| PRINT_SYS_TBL 22| 2019 -08-26 00：00：00| 9 |2023-09-13 00：00：00| 2023-09-21 00：00：00|
| J66| ILLUM_STIG93| 2023-09-10 00：00：00| 11 |2023-09-10 00：00：00| 2023-09-20 00：00：00：00|
| J789| DEF| 2023-09-16 00：00：00| 10 |2023-09-16 00：00：00| 2023-09-25 00：00：00|
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

如何使用Oracle SQL查询生成给定日期和间隔的日期行？

2条答案

相关问题

热门标签

最新问答