sql - 简单的SQL路径查询？

Question

我正在学习一本 SQL 入门教科书，对以下问题感到困惑，我们在其中获得了表和值:

CREATE TABLE LineageTable (
    parent INT,
    id INT,
    genus_name VARCHAR(30),
    PRIMARY KEY (id)
);

INSERT INTO LineageTable VALUES
    (3, 1, 'FamilyA'),
    (2, 4, 'FamilyB'),
    (7, 2, 'FamilyC');

我想写一个函数来返回一个文本字符串，表示从给定名称到所需根的路径

我的尝试:

CREATE FUNCTION LineageTable (input VARCHAR(50))
RETURNS TABLE (input VARCHAR(50))
AS $$
    BEGIN
        RETURN QUERY 
            SELECT input
            FROM LineageTable1 
            INNER JOIN LineageTable ON LineageTable.parent = LineageTable.id
            WHERE LineageTable1.genus_name = LineageTable1.genus_name;
    END $$

但是，我对如何多次遍历此表以正确地将路径串在一起感到困惑。有任何想法吗？谢谢大家!

Answer 1

在 Postgres 上你可以使用 RECURSIVE查询:

WITH RECURSIVE Rec as 
(
    SELECT id, parent_id, Name 
    FROM   Hierarchy
    WHERE  Name = 'Sirenia'
    UNION ALL
    SELECT     Hierarchy.id, Hierarchy.parent_id, Hierarchy.Name
    FROM       Hierarchy
    INNER JOIN Rec
    ON         Hierarchy.id = Rec.parent_Id
)
SELECT string_agg(Name, '->') path
FROM   Rec;

|                path               |
|:---------------------------------:|
| Sirenia->Paenungulata->Afrotheria |

Rextester here