Início / coding / Perguntas / 77902266
Accepted
Bob
Asked: 2024-01-30 03:50:58 +0800 CST

Loop SQL baseado no resultado da tabela

  • 772

É possível com uma iteração do Loop obter o seguinte resultado da tabela: Cada vez tirar de todos cd_number um número cada e suas datas. Tentei com um loop, mas não consegui iterá-lo da maneira que preciso. Pergunta SQL em detalhes Existe alguma outra maneira de recuperar o resultado específico além de usar um loop?

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 respostas

  1. Best Answer

    Use a ROW_NUMBERfunção analítica para gerar o número da iteração:

    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

    Que, para os dados de amostra:

    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;

    Saídas:

    ITERAÇÃO CD_NUMBER DATA_BEGIN DATA_END
    1 1 03/04/2017 00:00:00 03/06/2019 00:00:00
    1 2 02/11/2017 00:00:00 30/04/2018 00:00:00
    1 3 31/12/2016 00:00:00 30/10/2017 00:00:00
    2 1 25/02/2019 00:00:00 03/06/2019 00:00:00
    2 2 25/02/2019 00:00:00 11/10/2019 00:00:00
    2 3 2021-10-21 00:00:00 16/02/2022 00:00:00
    3 1 13/03/2020 00:00:00 30/06/2023 00:00:00
    3 3 2022-10-19 00:00:00 30/06/2023 00:00:00

    Se você deseja apenas uma iteração específica, enumere as iterações em uma subconsulta e filtre em uma consulta externa:

    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;

    Quais saídas:

    CD_NUMBER DATA_BEGIN DATA_END
    1 03/04/2017 00:00:00 03/06/2019 00:00:00
    2 02/11/2017 00:00:00 30/04/2018 00:00:00
    3 31/12/2016 00:00:00 30/10/2017 00:00:00

    violino

    • 1

relate perguntas