SQL查询：检查组内当前用户前置所有审批层级是否均通过

问题描述 #

现有#approvals表，每个int_id（申请单）对应6个appr_lvl（审批层级）的记录，每条记录包含对应user_id的appr_id（审批状态）。需要实现查询：判断当前用户的所有前置审批层级是否均已完成审批（appr_id < 3）。

示例数据 #

drop table if exists #approvals

create table #approvals 
(
    int_id int, 
    appr_lvl int,  
    role_id int, 
    user_id int, 
    appr_name nvarchar(max), 
    appr_date float, 
    appr_id int
)

insert into #approvals (int_id, appr_lvl, role_id, user_id, appr_name, appr_date, appr_id)
values (2260, 1, 18, 132, 'auto approved', 45281.6722664352, 1),
       (2260, 2, 7, 158, 'approved', 45281.6305555556, 2),
       (2260, 3, 2, 153, 'not approved', 0, 4),
       (2260, 4, 6, 69, 'not approved', 0, 4),
       (2260, 5, 8, 126, 'not approved', 0, 4),
       (2260, 6, 18, 132, 'auto approved', 45281.6722664352, 1);

select *
from #approvals

测试场景 #

• 查询user_id=153时，应返回int_id=2260：该用户处于第3审批层级，其appr_id=4（未审批），且1-2层级appr_id均<3；
• 查询user_id=80时，无返回：该用户不在审批列表中；
• 查询user_id=69时，无返回：1-3层级未全部满足appr_id<3。

解决方案 #

方法1：关联子查询 #

通过NOT EXISTS校验前置层级是否存在未完成审批的记录：

DECLARE @target_user_id INT = 153;

SELECT a.int_id
FROM #approvals a
WHERE a.user_id = @target_user_id
AND NOT EXISTS (
    SELECT 1
    FROM #approvals b
    WHERE b.int_id = a.int_id
    AND b.appr_lvl < a.appr_lvl
    AND b.appr_id >= 3
);

方法2：窗口函数+筛选 #

先统计每个申请单的前置未完成审批数量，再筛选目标用户的有效记录：

DECLARE @target_user_id INT = 153;

WITH user_approval_info AS (
    SELECT 
        int_id,
        appr_lvl,
        user_id,
        -- 统计当前申请单中，目标用户层级前未完成审批的数量
        SUM(CASE WHEN appr_lvl < (SELECT appr_lvl FROM #approvals WHERE int_id = a.int_id AND user_id = @target_user_id) AND appr_id >=3 THEN 1 ELSE 0 END) OVER (PARTITION BY int_id) AS uncompleted_prev_count,
        CASE WHEN user_id = @target_user_id THEN 1 ELSE 0 END AS is_target_user
    FROM #approvals a
)
SELECT DISTINCT int_id
FROM user_approval_info
WHERE is_target_user = 1
AND uncompleted_prev_count = 0;

核心逻辑 #

1. 定位目标用户在每个申请单中的审批层级；
2. 校验该申请单中所有低于目标层级的记录，是否存在appr_id >=3（未完成审批）的情况；
3. 仅返回前置层级全部完成审批的申请单int_id。

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