主页 / coding / 问题 / 77902266
Accepted
Bob
Asked: 2024-01-30 03:50:58 +0800 CST

基于表结果的 SQL 循环

  • 772

是否可以通过循环的一次迭代从表中获得以下结果：每次从每个人 cd_number 中获取一个数字及其日期。我尝试使用循环，但无法按照我需要的方式迭代它。SQL问题详细除了使用循环之外还有其他方法来检索特定结果吗？

table result; 
             cd_number  date_begin  date_end
                 1        03-APR-17   03-JUN-19
                 1        25-FEB-19   03-JUN-20
                 1        13-MAR-20   30-JUN-23
                 2        02-NOV-17   30-APR-18
                 2        25-FEB-19   11-OCT-19
                 3        31-DEC-16   30-OCT-17
                 3        21-OCT-21   16-FEB-22
                 3        19-OCT-22   30-JUN-23

           
             Example table with results
             CREATE TABLE tbl28 (cd_Number int, date_begin date, date_end date );

  insert into tbl28 (cd_Number,date_begin,date_end)
  SELECT 1,  '03-APR-17', '03-JUN-19'   FROM DUAL UNION ALL
  SELECT 1,  '25-FEB-19', '03-JUN-19'   FROM DUAL UNION ALL
  SELECT 1,  '13-MAR-20', '30-JUN-23'   FROM DUAL UNION ALL
  SELECT 2,  '02-NOV-17', '30-APR-18'   FROM DUAL UNION ALL
  SELECT 2,  '25-FEB-19', '11-OCT-19'   FROM DUAL UNION ALL
  SELECT 3,  '31-DEC-16', '30-OCT-17'   FROM DUAL UNION ALL
  SELECT 3,  '21-OCT-21', '16-FEB-22'   FROM DUAL UNION ALL
  SELECT 3,  '19-OCT-22', '30-JUN-23'   FROM DUAL;
 
      expected result;
   first iteration    1       03-APR-17   03-JUN-19
                      2       02-NOV-17   30-APR-18
                      3       31-DEC-16   30-OCT-17
   second iteration  
                      1       25-FEB-19   03-JUN-20   
                      2       25-FEB-19   11-OCT-19 
                      3       21-OCT-21   16-FEB-2 2
 and in the third iteration 
                      1       13-MAR-20   30-JUN-23
                      3       19-OCT-22   30-JUN-23
sql

1 个回答

  1. Best Answer

    使用ROW_NUMBER解析函数生成迭代次数：

    SELECT ROW_NUMBER() OVER (PARTITION BY cd_number ORDER BY date_begin) AS iteration,
       cd_number,
       date_begin,
       date_end
FROM   tbl28
ORDER BY iteration, cd_number

    其中，对于样本数据：

    CREATE TABLE tbl28 (cd_Number int, date_begin date, date_end date );

insert into tbl28 (cd_Number,date_begin,date_end)
  SELECT 1, DATE '2017-04-03', DATE '2019-06-03' FROM DUAL UNION ALL
  SELECT 1, DATE '2019-02-25', DATE '2019-06-03' FROM DUAL UNION ALL
  SELECT 1, DATE '2020-03-13', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 2, DATE '2017-11-02', DATE '2018-04-30' FROM DUAL UNION ALL
  SELECT 2, DATE '2019-02-25', DATE '2019-10-11' FROM DUAL UNION ALL
  SELECT 3, DATE '2016-12-31', DATE '2017-10-30' FROM DUAL UNION ALL
  SELECT 3, DATE '2021-10-21', DATE '2022-02-16' FROM DUAL UNION ALL
  SELECT 3, DATE '2022-10-19', DATE '2023-06-30' FROM DUAL;

    输出：

    迭代 CD_NUMBER DATE_BEGIN DATE_END
    1 1 2017-04-03 00:00:00 2019-06-03 00:00:00
    1 2 2017-11-02 00:00:00 2018-04-30 00:00:00
    1 3 2016-12-31 00:00:00 2017-10-30 00:00:00
    2 1 2019-02-25 00:00:00 2019-06-03 00:00:00
    2 2 2019-02-25 00:00:00 2019-10-11 00:00:00
    2 3 2021-10-21 00:00:00 2022-02-16 00:00:00
    3 1 2020-03-13 00:00:00 2023-06-30 00:00:00
    3 3 2022-10-19 00:00:00 2023-06-30 00:00:00

    如果您只想要特定的迭代，则枚举子查询中的迭代，然后在外部查询中进行过滤：

    SELECT cd_number,
       date_begin,
       date_end
FROM   (
  SELECT ROW_NUMBER() OVER (PARTITION BY cd_number ORDER BY date_begin) AS iteration,
         cd_number,
         date_begin,
         date_end
  FROM   tbl28
  ORDER BY iteration, cd_number
)
WHERE  iteration = 1;

    哪个输出：

    CD_NUMBER DATE_BEGIN DATE_END
    1 2017-04-03 00:00:00 2019-06-03 00:00:00
    2 2017-11-02 00:00:00 2018-04-30 00:00:00
    3 2016-12-31 00:00:00 2017-10-30 00:00:00

    小提琴

    • 1

相关问题