如何创建支持WHERE子句的递归VIEW以查询犬只祖先树？

问题出在普通递归VIEW的写法没有保留起始节点的标识，导致WHERE子句只能筛选当前节点ID，无法关联到整个祖先链。下面是具体的解决方案：

1. 先明确表结构（基于常规场景假设） #

假设你的表结构如下：

-- 犬只信息表
CREATE TABLE dog (
    dog_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- 犬只亲子关系表
CREATE TABLE dog_parent (
    dog_id INT NOT NULL,
    parent_id INT NOT NULL,
    PRIMARY KEY (dog_id),
    FOREIGN KEY (dog_id) REFERENCES dog(dog_id),
    FOREIGN KEY (parent_id) REFERENCES dog(dog_id)
);

2. 创建带起始节点标识的递归VIEW #

核心思路是在递归CTE中保留起始节点的ID，让每条记录都能关联到它的根节点（即你要查询的起始dog_id）：

CREATE VIEW dog_ancestor_tree AS
WITH RECURSIVE ancestor_cte AS (
    -- 起始分支：每个犬只作为根节点，记录起始ID和当前节点信息
    SELECT
        d.dog_id AS start_dog_id,
        d.dog_id AS current_dog_id,
        d.name AS current_name,
        dp.parent_id
    FROM dog d
    LEFT JOIN dog_parent dp ON d.dog_id = dp.dog_id
    
    UNION ALL
    
    -- 递归分支：沿着父级向上遍历，始终保留起始节点ID
    SELECT
        ac.start_dog_id,
        d.dog_id AS current_dog_id,
        d.name AS current_name,
        dp.parent_id
    FROM ancestor_cte ac
    JOIN dog_parent dp ON ac.parent_id = dp.dog_id
    JOIN dog d ON dp.parent_id = d.dog_id
)
SELECT * FROM ancestor_cte;

3. 查询完整祖先树 #

通过筛选start_dog_id指定起始节点，就能获取该节点的完整祖先链（包括自身、直接父级、祖父级等所有祖先）：

-- 查询dog_id=26的完整祖先树
SELECT * FROM dog_ancestor_tree WHERE start_dog_id = 26;

为什么之前的VIEW不生效？ #

如果你的递归VIEW没有保留start_dog_id，而是直接用dog_id作为唯一标识，那么WHERE子句dog_id=26只会筛选出当前节点ID为26的记录，而递归生成的父级、祖父级记录的dog_id并不是26，自然不会被包含在结果中，只能看到直接关联的父级记录。

内容的提问来源于stack exchange，提问作者skorpio