如何在递归SQL查询中按子请求合法状态返回true/false布尔值

修改后的查询代码 #

DECLARE @main_parent_id bigint = 1

;WITH cte AS (
    -- 先取根节点的所有直接/间接子请求，不提前过滤状态
    SELECT id, state
    FROM tbl_request 
    WHERE parent_id = @main_parent_id
    UNION ALL
    SELECT r.id, r.state
    FROM tbl_request r
    JOIN cte ON r.parent_id = cte.id
)
-- 只要存在任意状态属于非法列表的子请求，就返回false，否则返回true
SELECT CASE 
    WHEN EXISTS(SELECT 1 FROM cte WHERE state IN ('not-legit' /* 其他非法状态值可追加到此处 */)) 
    THEN CAST(0 AS BIT) 
    ELSE CAST(1 AS BIT) 
END AS all_children_legit

逻辑说明 #

• 原CTE提前用NOT IN过滤了非法状态的请求，导致非法记录不会进入递归结果集，无法判断是否存在非法请求，因此调整了递归逻辑：先不做状态过滤，查出指定根节点下所有层级的全部子请求，保留state字段用于后续判断
• 用EXISTS关键字判断是否存在非法请求，只要匹配到第一条非法记录就会终止查询，性能远高于全量遍历统计，你可以将所有非法状态值都放到IN的列表中，适配多非法状态的判断需求
• 返回的BIT类型值对应布尔结果：1等价true（所有子请求均合法），0等价false（存在至少一个非法子请求），你可以根据业务需要调整返回的true/false对应规则

多根节点批量查询适配 #

如果@main_parent_id需要从其他根节点查询批量获取，可以改用表变量存储根ID列表，关联CTE做批量判断，示例如下：

-- 先获取所有根节点ID（此处替换为你自己的根节点查询逻辑）
DECLARE @root_ids TABLE (root_id bigint)
INSERT INTO @root_ids(root_id)
SELECT id FROM tbl_request WHERE parent_id IS NULL /* 你的根节点过滤条件 */

;WITH cte AS (
    SELECT r.parent_id AS root_id, r.id, r.state
    FROM tbl_request r
    JOIN @root_ids ri ON r.parent_id = ri.root_id
    UNION ALL
    SELECT cte.root_id, r.id, r.state
    FROM tbl_request r
    JOIN cte ON r.parent_id = cte.id
)
SELECT 
    root_id,
    CASE WHEN EXISTS(SELECT 1 FROM cte c WHERE c.root_id = ri.root_id AND c.state IN ('not-legit')) THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS all_children_legit
FROM @root_ids ri

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