编写SQL查询根据指定Location ID返回对应父级及自身位置记录

现有表结构 #

需求 #

• 当指定location id=3时，返回id为1、2、3的记录（排除id=4）；
• 当指定location id=2时，返回id为1、2的记录（排除id=3、4）；
• 当指定location id=4时，返回所有id为1、2、3、4的记录。

解决方案 #

核心思路是先获取目标ID对应的层级数据和层级深度，再匹配所有前缀层级一致且自身层级深度不超过目标深度的记录。以下是具体SQL语句：

WITH target_loc AS (
    SELECT 
        level1, level2, level3, level4,
        -- 计算目标记录的层级深度
        CASE 
            WHEN level4 IS NOT NULL THEN 4
            WHEN level3 IS NOT NULL THEN 3
            WHEN level2 IS NOT NULL THEN 2
            WHEN level1 IS NOT NULL THEN 1
            ELSE 0
        END AS target_depth
    FROM location
    WHERE id = ? -- 替换为需要指定的ID（如2、3、4）
)
SELECT l.*
FROM location l
JOIN target_loc t ON 
    -- 确保一级层级完全匹配
    l.level1 = t.level1
    -- 若目标有二级层级，当前记录要么匹配二级层级，要么无二级层级（结合深度限制）
    AND (t.level2 IS NULL OR l.level2 = t.level2)
    -- 同理匹配三级层级
    AND (t.level3 IS NULL OR l.level3 = t.level3)
    -- 同理匹配四级层级
    AND (t.level4 IS NULL OR l.level4 = t.level4)
    -- 当前记录的层级深度不超过目标深度
    AND CASE 
            WHEN l.level4 IS NOT NULL THEN 4
            WHEN l.level3 IS NOT NULL THEN 3
            WHEN l.level2 IS NOT NULL THEN 2
            WHEN l.level1 IS NOT NULL THEN 1
            ELSE 0
        END <= t.target_depth
WHERE l.is_active = true;

逻辑说明 #

1. 通过CTE target_loc 获取指定ID对应的各层级值和层级深度；
2. 关联主表时，确保所有层级前缀完全匹配（比如目标是A>B>C，当前记录的level1必须是A，level2必须是B，level3要么是C要么为null）；
3. 通过层级深度限制，过滤掉比目标层级更深的记录（比如目标是3级时，排除4级的id=4）。

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