这在 SQL 中可能吗？

Question

假设我有一个名为“species”的表，其中包含 3 列：“Id”、“ancestorId”和“name”。 'ancestorId' 是祖先物种的 'Id'，因此，例如，如果智人的祖先是南方古猿，而南方古猿的 'Id' 是 5，那么智人的 'ancestorId' 就是 5。我们还可以说，有一种名为“第一个货币”的货币，其“Id”为 0 或 null。所以我想要的是从特定物种（例如智人）中选择一个祖先列表到“第一个物种”，无论途中有多少个节点。这可以用 SQL 实现吗？

Answer 1

您很可能不想让您的第一个物种的 ID 为 null。 null 与 0 有很大不同。null 意味着你不知道该值是什么。 0 表示值为 0。注意 null <> null 且 0 = 0。这可能会影响您搜索识别第一个物种的方式。

Answer 2

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 

Answer 3

是的，我可以用 SQL 实现。您可以为此目的使用递归查询。请查看此处。阅读整页。

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
;

编辑
这适用于 ORACLE、SQL Server、PostgreSQL 以及任何其他具有递归查询支持的数据库。要处理不支持递归查询的数据库，您将不得不使用嵌套集模型。

Answer 4

在 MySQL 中管理分层数据 是一个很好的资源，适合您的需求正在谈论，特别是如果您正在使用不支持递归查询的数据库系统。它讨论了您需要如何构建数据以便更轻松地完成您想做的事情。

Answer 5

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 Server 2005+
• Oracle 11gR2
• PostgreSQL 8.4+

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