如何用SQL实现基于组织层级的动态过滤视图？

可行性结论 #

完全可以实现。通过**递归CTE（公共表表达式）**遍历组织架构的层级关系，结合数据库的当前登录用户函数，就能动态过滤出当前用户及其下属的发票数据。

假设的核心表结构 #

基于需求定义三张表的关键字段（可根据实际业务调整）：

• 组织架构表org_chart：
  • employee_id：员工ID（主键）
  • manager_id：直属上级ID（外键关联用户表，顶级管理者值为NULL）
• 用户表users：
  • user_id：用户ID（主键）
  • username：用户名（如'Jack'、'Nicole'）
  • department：所属部门（如'IT'）
• 发票表invoice：
  • invoice_id：发票ID（主键）
  • user_id：关联的用户ID（外键关联用户表）
  • 其他业务字段（如金额、开票日期等）

动态过滤视图的SQL实现 #

以下是兼容MySQL 8.0+、SQL Server、PostgreSQL的通用写法：

CREATE VIEW filtered_invoices AS
WITH RECURSIVE user_hierarchy AS (
    -- 第一步：获取当前登录用户的ID
    SELECT u.user_id
    FROM users u
    -- 不同数据库替换对应获取当前用户的函数：
    -- MySQL用SUBSTRING_INDEX(CURRENT_USER(), '@', 1)提取纯用户名
    -- SQL Server用SUSER_SNAME()或CURRENT_USER
    -- PostgreSQL直接用current_user
    WHERE u.username = SUBSTRING_INDEX(CURRENT_USER(), '@', 1)
    UNION ALL
    -- 第二步：递归遍历当前用户的所有下属
    SELECT oc.employee_id
    FROM org_chart oc
    JOIN user_hierarchy uh ON oc.manager_id = uh.user_id
)
-- 关联发票表，只返回当前用户及下属的发票
SELECT i.*
FROM invoice i
JOIN user_hierarchy uh ON i.user_id = uh.user_id;

关键说明 #

• 递归层级遍历：user_hierarchy会先定位当前登录用户，再逐层递归拉出其所有下属的ID，不管层级有多深。
• 动态适配用户：每次查询视图时，数据库会自动识别当前登录用户，重新计算可访问的用户范围，无需手动修改视图逻辑。
• 权限控制延伸：如果需要更精细的权限（比如限制部门），可以在递归CTE中加入department过滤条件，确保跨部门的上下级无法访问对方数据。

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