按权限层级统计项目开合计数的SQL子查询需求

前置说明 #

• 预设入参：@current_user_emp_id 为当前登录用户的员工ID，用于权限范围校验
• 核心统计口径严格匹配需求：
  • open列：统计Sales表中项目状态ID为1、2、3、5的项目数量
  • close列：统计Sales表中项目状态ID为4的项目数量
• 权限规则匹配：
  • 经理身份（存在直属下属）：返回所有1级直属下属的项目统计数据
  • 普通员工（无直属下属）：仅返回本人的项目统计数据

可直接复用的SQL子查询 #

WITH allowed_emp_scope AS (
    -- 获取经理身份下的所有1级直属下属ID
    SELECT EmpId
    FROM EmpManager
    WHERE EmpManagerId = @current_user_emp_id

    UNION

    -- 普通员工身份下仅保留本人ID
    SELECT EmpId
    FROM Emp
    WHERE EmpId = @current_user_emp_id
      AND NOT EXISTS (
          SELECT 1 
          FROM EmpManager 
          WHERE EmpManagerId = @current_user_emp_id
      )
)
-- 按员工维度聚合统计开放、关闭项目数
SELECT
    e.EmpId,
    e.Ename,
    COUNT(CASE WHEN s.Status IN (1,2,3,5) THEN 1 END) AS open,
    COUNT(CASE WHEN s.Status = 4 THEN 1 END) AS close
FROM Emp e
INNER JOIN allowed_emp_scope aes
    ON e.EmpId = aes.EmpId
LEFT JOIN Sales s
    ON e.EmpId = s.EmpId
GROUP BY e.EmpId, e.Ename;

逻辑校验说明 #

• CTE部分自动根据当前用户的下属存在情况判定权限范围，无需额外编写身份判断分支
• 条件聚合的写法避免了多次关联Sales表取计数，执行效率更高
• 字段命名完全匹配需求要求的open、close虚拟列，无需额外转换
• 采用员工维度左关联Sales表的写法，确保权限范围内无项目记录的员工也会返回计数为0的结果，避免漏统计

内容的提问来源于stack exchange，提问作者isha bhardwaj