sql - 从层次结构中的根获取最远的值

Question

我正在尝试对“设置”的层次结构进行建模，其中值在根级别定义，并且可以被更具体的子级覆盖。如果子项未指定值，则应使用其父项值。

我将使用一个陈腐的例子来说明问题。这里的层次结构只有三层深，但我想要一个适用于 N 层的解决方案。

鉴于我存储的信息如下:

id| parent_id| setting
----------------------
 1|      NULL|   false
 2|         1|    true
 3|         2|    NULL

从程序的角度来看，我想要的是在树中获取一个子节点，如果它的“设置”值为 NULL，则递归地查看其父节点的值，直到找到一个值，或者根是到达。本质上，对于所提供的信息，我想生成以下集合，因此我可以附加一个简单的 WHERE 子句来获取任何给定 id 的适用设置。

id| setting
-----------
 1|   false
 2|    true
 3|    true

我有一个 View 将层次结构“扁平化”为祖先和后代:

ancestor| descendant| ancestor_setting| descendant_setting
----------------------------------------------------------
       1|          2|            false|               true
       1|          3|            false|               NULL
       2|          3|             true|               NULL
    NULL|          1|             NULL|              false
    NULL|          2|             NULL|               true
    NULL|          3|             NULL|               NULL

通过这种方式，您可以将层次结构的所有级别作为一个集合进行查询，我希望这对获得答案有用。

到目前为止，我只能使用此 View 从树中选择一个“分支”:

SELECT COALESCE(ancestor, descendent) id,
       CASE WHEN ancestor IS NULL THEN descendant_setting
            ELSE ancestor_setting
       END setting
FROM hierarchy
WHERE descendant = 3

id| setting
-----------
 1|   false
 2|    true
 3|    NULL

我已经尝试想出使用这种“扁平化”结构来形成一组简单连接的方法，虽然我可以通过这种方式取回所有记录(然后在客户端上按程序过滤它们)，但我想要了解是否有办法生成预期的设置，以便我可以取回单个 ID 的预期设置。

Answer 1

WITH    RECURSIVE
        q AS
        (
        SELECT  id, parent_id, id ancestor_id, setting
        FROM    mytable
        WHERE   parent_id IS NULL
        UNION ALL
        SELECT  m.id, q.id, ancestor_id, COALESCE(m.setting, q.setting)
        FROM    q
        LEFT JOIN
                mytable m
        ON      m.parent_id = q.id
        WHERE   q.id IS NOT NULL
        )
SELECT  *
FROM    q
WHERE   id IS NULL