sql - 一个表的非常困难的递归 SQL 查询

Question

我想在表中找到链。表 X 由 3 列组成

column1, column2, column3
wolf,     eat,     cat
cat,      eat,     mouse

我进行查询以查找“链”

SELECT t1.column1, t2.column3
FROM X AS t1
JOIN X AS t2
ON t1.column3 = t2.column1

这里我有一条链子

wolf,     eat,     cat,    cat,      eat,     mouse

然后我可以展示

wolf, mouse

但是下一个数据的查询是什么？

column1, column2, column3
human,   eat,    bear
bear,    eat,    wolf
wolf,    eat,    cat
cat,     eat,    mouse

我想从column1中的任意生物开始，在column3中找到链的末端来检查X是否吃掉了Y。

测试：

Show human->wolf
Show human->mouse
Show bear->mouse

我不知道如何在这里找到进行递归查询的步骤数。

标签： sqlhierarchical-datarecursive-query

Answer 1

The second column isn't really relevant to your question so this boils down to a "standard" recursive common table expression where the "child" references the "parent" row.

The following is standard ANSI SQL, you might need to adjust that to the DBMS you actually use.

with recursive food_chain as (
  select col1, col3, col1||' '||col2||' '||col3 as chain
  from x
  where col1 = 'human'
  union all
  select c.col1, c.col3, p.chain||', '||c.col1||' '||c.col2||' '||c.col3
  from x as c
   join food_chain p on c.col1 = p.col3
)
select *
from food_chain;

This would result in e.g.:

col1  | col3  | chain                                                     
------+-------+-----------------------------------------------------------
human | bear  | human eat bear                                            
bear  | wolf  | human eat bear, bear eat wolf                             
wolf  | cat   | human eat bear, bear eat wolf, wolf eat cat               
cat   | mouse | human eat bear, bear eat wolf, wolf eat cat, cat eat mouse

As you are interested only in the last (final) food chain, you can do that by adding a counter that identifies the level and select only that:

with recursive food_chain as (
  select col1, col3, col1||' '||col2||' '||col3 as chain, 1 as level
  from x
  where col1 = 'human'
  union all
  select c.col1, c.col3, p.chain||', '||c.col1||' '||c.col2||' '||c.col3, p.level + 1
  from x as c
   join food_chain p on c.col1 = p.col3
)
select chain
from food_chain
order by level desc
fetch first 1 rows only

Returns:

chain                                                     
----------------------------------------------------------
human eat bear, bear eat wolf, wolf eat cat, cat eat mouse

The starting point of the chain is defined by the condition in the non-recursive part of the CTE: where col1 = 'human'

Online example: http://rextester.com/GKTU52621