基于用户角色的PROJECT表条件查询：STAFF与ADMIN权限控制

核心逻辑 #

• 若用户为ADMINISTRATOR（管理员）：可查看所有项目，无额外过滤限制
• 若用户为STAFF（普通员工）：仅能查看PROJECT_USER表中分配给自己的项目

表关联字段说明（按需替换） #

先明确各表关键关联字段，若你的表字段名不同，直接对应替换即可：

• PROJECT表：project_id（项目唯一标识）
• USER表：user_id（用户唯一标识）
• PROJECT_USER表：存储项目与用户的关联关系，含project_id、user_id字段
• ROLE表：role_id（角色唯一标识）、role_name（角色名称，值为STAFF或ADMINISTRATOR）
• USER_ROLE表：存储用户与角色的关联关系，含user_id、role_id字段

WHERE子句实现代码 #

假设当前查询的用户ID为@current_user_id（替换为你实际使用的变量或具体值），WHERE子句写法如下：

WHERE
    -- 管理员权限判断：只要用户拥有管理员角色，即可查看所有项目
    EXISTS (
        SELECT 1
        FROM USER_ROLE ur
        JOIN ROLE r ON ur.role_id = r.role_id
        WHERE ur.user_id = @current_user_id
          AND r.role_name = 'ADMINISTRATOR'
    )
    -- 普通员工权限判断：先确认是员工角色，再过滤出分配给自己的项目
    OR (
        EXISTS (
            SELECT 1
            FROM USER_ROLE ur
            JOIN ROLE r ON ur.role_id = r.role_id
            WHERE ur.user_id = @current_user_id
              AND r.role_name = 'STAFF'
        )
        AND EXISTS (
            SELECT 1
            FROM PROJECT_USER pu
            WHERE pu.project_id = PROJECT.project_id
              AND pu.user_id = @current_user_id
        )
    )

简化写法（适合单一角色场景） #

如果每个用户仅对应一种角色，可使用CASE语句简化逻辑：

WHERE
    CASE
        WHEN (SELECT r.role_name FROM USER_ROLE ur JOIN ROLE r ON ur.role_id = r.role_id WHERE ur.user_id = @current_user_id) = 'ADMINISTRATOR'
            THEN 1 = 1 -- 管理员无项目限制
        ELSE
            EXISTS (SELECT 1 FROM PROJECT_USER pu WHERE pu.project_id = PROJECT.project_id AND pu.user_id = @current_user_id) -- 员工仅查看自己的项目
    END

注意事项 #

• 若用户同时拥有两种角色，上述写法会优先按管理员权限处理，符合权限优先级逻辑
• 请将@current_user_id替换为实际获取当前用户ID的方式，比如应用传参、会话变量等

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