在SQL中这可能吗?

问题描述

假设我有一个名为"species"的表，该表具有3列:"Id"，"ancestorId"和"name". "ancestorId"是祖先物种的"Id"，因此，例如，如果高智人的祖先是南方古猿，而南方古猿的"Id"是5，那么高智人的祖先Id"是5.有一个名为第一类"的物种，其"Id"为0或为null.所以我想要的是从一个特定物种(例如，智人)到第一物种"中选择一个祖先列表，无论途中有多少个节点.使用SQL可以做到这一点吗?

Let's say I have a table called 'species' with 3 columns: 'Id', 'ancestorId' and 'name'. The 'ancestorId' is the 'Id' of the ancestor specie, so for example if the ancestor of the homo sapiens is the australopithecus, and australopithecus's 'Id' is 5, then homo sapiens's 'ancestorId' is 5. Let's say, also, that there is a specie called 'First specie' whose 'Id' is either 0 or null. So what I want is to select a list of ancestors from a particular specie, say, homo sapiens, to the 'First specie', no matter how many nodes are on the way. Is this possible with SQL?

推荐答案

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

The ANSI way is to use a recursive WITH clause:

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 +

自v2起，Oracle使用CONNECT BY语法提供了分层查询支持.

Oracle's had hierarchical query support since v2, using CONNECT BY syntax.

这篇关于在SQL中这可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！