主页 / 问题 / 1275920
Accepted
Gipnomanul
Asked:2022-04-28 17:40:06 +0000 UTC2022-04-28 17:40:06 +0000 UTC

如何计算链中的记录数？

  • 772

我有一张旅客列车运动的简化表，显示了它从一个城市到另一个城市的运动：

create table testt (
    ID NUMBER,
    VALST VARCHAR2(255),
    VALEND VARCHAR2(255),
    NEXTID NUMBER
)

   ID VALST  VALEND NEXTID
----- ----- ------- ------
    1   MOS     SAN      2
    2   SAN     MOS      
    3   MOS     EKB      4
    4   EKB     RYA      5
    5   RYA     SAN      6
    6   SAN     MOS      
    7   MOS     KRA      8
    8   KRA     VOR      9
    9   VOR     MOS

它的工作原理如下：单元格VALST显示火车从VALEND哪里出发，到达哪里。该单元格NEXTID包含下一个航班的 ID。也就是说，例如，根据 ID 1，火车会先从 MOS 开往 SAN，然后在 ID 2 下的下一趟行程中，它会从 SAN 开往 MOS，链条将在那里结束，因为没有下一个标识符。

问题如下：确定链中的行程次数，即火车何时离开MOS，最终返回MOS。

在上面的示例中，从 ID 1 开始，链大小为 2，从 ID 3 开始，链大小为 4。

我尝试使用 LEAD 功能，但不幸的是，它不起作用，请帮助。

sql

2 个回答

  1. Best Answer

    数据处于反向层次结构中。

    通过一个简单的分层查询（在db<>fiddle上）解决：

    select valst, level numoftrips, sys_connect_by_path (valst, ' >> ') trips
from testt t
where connect_by_isleaf = 1
start with nextid is null 
connect by nextid = prior id

    结果：

    VALST  NUMOFTRIPS TRIPS                            
------ ---------- --------------------------------
MOS             2  >> SAN >> MOS                  
MOS             4  >> SAN >> RYA >> EKB >> MOS    
MOS             3  >> VOR >> KRA >> MOS
    • 4

  2. 这就是它在 MySQL 中的样子（对不起，我不是为 ORACLE 做的，将数据输入到 fiddle 中确实很痛苦，而且为了诊断问题，它完全被撕掉了，但看起来几乎一样）：

    WITH RECURSIVE
cte1 AS ( SELECT ID, VALST, VALEND, NEXTID, 1 AS length, ROW_NUMBER() OVER (ORDER BY ID) chain
          FROM testt
          WHERE NEXTID IS NULL
          UNION ALL
          SELECT testt.ID, testt.VALST, cte1.VALEND, testt.NEXTID, cte1.length + 1, cte1.chain
          FROM testt
          JOIN cte1 ON testt.NEXTID = cte1.ID ),
cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY chain ORDER BY length DESC) rn
          FROM cte1 )
SELECT ID, VALST, length 
FROM cte2
WHERE rn = 1;

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a41425e1b0c3cb532c410351a95f8ace

    • 1

相关问题