条件递归SQL选择 [英] Conditional Recursive SQL Select

问题描述

请考虑以下数据库表.它由3列组成: Id ， ParentId ， Enabled .

Consider the following database table. It consists of 3 columns: Id, ParentId, Enabled.

我想产生类似于以下内容的结果集.基本上，对于每个具有 Parent ID 的记录，我想显示一个附加列 Enabled Parent ID .该列基本上需要递归检查密钥的层次结构，并在找到 Enabled = True 的密钥时停止.

I would like to produce a result set similar to the following. Basically for each record that has a Parent ID, I want to to display an additional column Enabled Parent Id. This column basically needs to recursively checks the hierarchy of the key, and stops when a key that is Enabled = True is found.

我希望这是即时实现的，而无需在表中添加任何其他计算列.

I would like this to achieve this on the fly, without requiring to add any additional computed columns in the table.

也许可以使用 CTE 来实现.

推荐答案

尝试以下CTE查询:

WITH T1 as 
(SELECT id,
        parentId,
        NULL as EnabledParentId, 
        ParentID as NextParent 
        FROM T
        WHERE ParentID is not null
UNION ALL
SELECT T1.id, 
       T1.parentId, 
       CASE WHEN T.enabled = 1 
            THEN T.ID 
            ELSE NULL END 
              as EnabledParentId,
       T.ParentID as NextParent
 FROM T1 
 JOIN T ON T1.NextParent = T.Id
 WHERE (nextParent is not Null) and (EnabledParentId IS NULL)
)
SELECT ID,
       ParentID,
       EnabledParentID 
       FROM T1 
       WHERE EnabledParentId IS NOT NULL 
             OR NextParent IS NULL
       ORDER BY ID;

这篇关于条件递归SQL选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！