sql—如何使用递归cte查找最后一个“链中的链接”

vybvopom 于 2021-07-17 发布在 Java

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

我很接近这个，但遗漏了一些东西。如何只获取链（如a->b、b->c）中的第一个和最后一个链接？我怎么才能得到a->c？

CREATE TEMP TABLE IF NOT EXISTS chains (
    cname TEXT PRIMARY KEY,
    becomes TEXT
);

INSERT INTO chains
VALUES
    ('A', NULL),
    ('B', 'C'),
    ('C', 'D'),
    ('D', 'E'),
    ('E', NULL)
;

WITH RECURSIVE
final_link AS (
SELECT
    chains.cname,
    chains.becomes
FROM
    chains

UNION

SELECT
    chains.cname,
    final_link.becomes
FROM
    chains
    INNER JOIN final_link
    ON chains.becomes = final_link.cname
)
SELECT * FROM final_link;

我想要的结果是：

cname | becomes
------|--------
'B'   | 'E'
'C'   | 'E'
'D'   | 'E'

sql postgresql select recursive-query common-table-expression

来源：https://stackoverflow.com/questions/62552434/how-do-i-find-the-final-link-in-the-chain-using-a-recursive-cte

2条答案

按热度按时间

dfddblmv1#

您可以通过以下方法实现此目的：仅使用链末端（而不是所有链接）开始递归，然后像您已经在做的那样迭代地预先设置链接：

WITH RECURSIVE final_link AS (
  SELECT cname, becomes
  FROM chains c
  WHERE (SELECT becomes IS NULL FROM chains WHERE cname = c.becomes)
UNION
  SELECT c.cname, fl.becomes
  FROM chains c
  INNER JOIN final_link fl ON c.becomes = fl.cname
)
SELECT * FROM final_link;

（演示）

赞(0）回复(0）举报 2021-07-24

col17t5w2#

这里有一种方法：

with recursive final_link as (
    select cname, becomes, cname original_cname, 0 lvl 
    from chains 
    where becomes is not null
    union all
    select c.cname, c.becomes , f.original_cname, f.lvl + 1
    from chains c
    inner join final_link f on f.becomes = c.cname
    where c.becomes is not null
)
select distinct on (original_cname) original_cname, becomes 
from final_link 
order by original_cname, lvl desc

其思想是让子查询跟踪起始节点以及树中每个节点的级别。然后可以使用 distinct on 在外部查询中。
db小提琴演示：

original_cname | becomes
:------------- | :------
B              | E      
C              | E      
D              | E

赞(0）回复(0）举报 2021-07-24

我来回答

sql—如何使用递归cte查找最后一个“链中的链接”

2条答案

相关问题

热门标签

最新问答