这在SQL中是可能的吗？

Question

假设我有一个名为'species‘的表，它有3列：'Id’、'ancestorId‘和'name’。“ancestorId”是祖先物种的“Id”，例如，如果智人的祖先是南方古猿，而南方古猿的“Id”是5，那么智人的“ancestorId”就是5。所以我想要的是选择一个祖先列表，从一个特定的物种，比方说，智人，到“第一个物种”，无论有多少节点在路上。使用SQL可以做到这一点吗？

Answer 1

ANSI的方法是使用递归的WITH子句：

WITH hierarchy AS (
  SELECT t.id, 
         t.name,
         t.ancestor
    FROM TABLE t
   WHERE t.ancestor IS NULL
  UNION
  SELECT t.id, 
         t.name,
         t.ancestor
    FROM TABLE t
    JOIN hierarchy h ON h.ancestorid = t.id)
SELECT *
  FROM hierarchy

支持：

• SQL 8.4+2005+
• 11gR2
• PostgreSQL 8.4+

Oracle从v2开始就支持使用CONNECT BY语法的分层查询。

Answer 2

对于您所讨论的内容，Managing Hierarchical Data in MySQL是一个很好的资源，特别是当您使用的数据库系统不支持递归查询时。它讨论了您需要如何组织数据，以便更轻松地完成您想要的操作。

Answer 3

是的，这在SQL中是可能的。为此，您可以使用递归查询。看看here吧。请阅读完整的页面。

WITH req_query AS 
(
  SELECT  id, 
          name,
          ancestorid
  FROM    your_table
  WHERE   name='homo sapiens'    //If you want to search by id, then change this line

  UNION

  SELECT  yt.id, 
          yt.name,
          yt.ancestorid
  FROM    your_table yt,
          req_query rq
  WHERE   yt.id = rq.ancestorid
          AND rq.id != 0
          AND rq.id is not null
)

SELECT  * 
FROM    req_query
;

这将与，SQL ，PostgreSQL和任何其他支持递归查询的数据库一起使用。要处理不支持递归查询的数据库，您必须使用嵌套集合模型。