sql - PostgreSQL 中的递归

Question

假设我们有一个表borders(country1,country2)，其中包含两个彼此接壤的国家，例如。 (瑞典、挪威)等。我想找到从给定国家(例如瑞典)仅通过边境口岸可以到达的所有国家。

这是我的解决方案的第一部分:

WITH RECURSIVE border(countryin) AS (
    select distinct country 
    from (select country2::character varying(4) as country 
          from borders where country1 = 'S'  
          union  
          select country1::character varying(4) as country 
          from borders where country2 = 'S' ) a
    UNION 
    select distinct sp.country::varchar(4) 
    from (select country1::varchar(4) as country, country2 as n 
          from borders) sp  
    join (select country2::varchar(4) as country, country1 as n, countryin as temp 
          from borders, border) st 
      on sp.country = st.n 
     and sp.country in st.temp
    where true              
)
SELECT distinct countryin, name 
FROM border, country 
where countryin = code ;

我唯一无法开始工作的是如何设置约束，以便结果边界表中存在特定国家/地区。我尝试在 st.temp 中使用 和 sp.country 以及其他几种方法，但我无法让它工作。

谁能告诉我如何解决这个问题？

当前结果:

• 现在，我收到一条错误消息“错误:“st”处或附近的语法错误LINE 4: ...s, border) st on sp.country = st.n and sp.country in st.temp"

期望的结果

• 列出可以使用从“S”开始的边界递归到达的所有县。因此，如果我们有 (S,N)、(N,R)、(R,C)、(D,A)，我们将得到:(N,R,C)

Answer 1

我相信还有改进的余地，但似乎要完成这项工作。

基本情况，你得到“S”出现在任何一边的“其他”国家

递归情况 获得与旅行路径中已有国家接壤的新国家，但避开带有“S”的国家，因此不会返回原点。还包括一个变量来跟踪递归深度，因此不会永远循环。 (不记得现在有多少个国家了)。

完成后，我添加过滤器 DISTINCT 以删除重复项。

也许我可以在 递归案例 上添加一个过滤器，以避免返回相同的国家/地区。不确定哪个更有效。

AND (    b.country1 NOT IN (SELECT country FROM Travel)
     AND b.country2 NOT IN (SELECT country FROM Travel)
    )

SQL Fiddle DEMO

WITH RECURSIVE travel(r_level, country) AS (
    select distinct 1 as r_level,
                    CASE WHEN country1 = 'S' THEN country2
                         ELSE country1          
                    END as country
    from borders 
    where country1 = 'S' 
       or country2 = 'S'    
    UNION 
    select distinct t.r_level + 1 as r_level,
                    CASE WHEN b.country1 = t.country THEN b.country2
                         ELSE b.country1
                    END as country           
    from borders b
    join travel  t
      ON (b.country1 = t.country OR b.country2 = t.country)
     AND (b.country1 <> 'S' AND b.country2 <> 'S')
    WHERE t.r_level < 300
)
SELECT DISTINCT country
FROM travel

输出

| country |
|---------|
|       N |
|       R |
|       C |

请随时提供更多国家/地区的更完整的 sqlFiddle 以改进测试。