SQL Server：查询John拥有但Bob无访问权限的屏幕列表

嘿，这个需求我经常碰到，给你两种实用的实现思路，一种是直接筛选结果（高效直观），另一种用Pivot做权限对比表，方便你整体查看差异：

先假设你的表结构大概是这样的（如果和实际有出入，你可以调整字段名）：

• Screen表：ScreenID（主键）、ScreenName（屏幕名称）
• Access表：UserName（用户名）、ScreenID（关联Screen表）、HasAccess（bit类型，1=有权限，0=无；或者如果只有有权限的用户才会有记录，那这个字段可以忽略）

这种方式最直接，性能也不错，适合快速拿到结果：

场景A：Access表只存储有权限的用户记录 #

SELECT s.ScreenName
FROM Screen s
-- 关联John的权限记录，确保John有这个屏幕权限
JOIN Access a_john ON s.ScreenID = a_john.ScreenID AND a_john.UserName = 'John'
-- 左关联Bob的权限记录，筛选出Bob没有的情况
LEFT JOIN Access a_bob ON s.ScreenID = a_bob.ScreenID AND a_bob.UserName = 'Bob'
WHERE a_bob.ScreenID IS NULL;

场景B：Access表包含所有用户的权限记录（包括无权限） #

SELECT s.ScreenName
FROM Screen s
-- 筛选出John有权限的屏幕
JOIN Access a_john ON s.ScreenID = a_john.ScreenID AND a_john.UserName = 'John' AND a_john.HasAccess = 1
-- 关联Bob的权限记录
LEFT JOIN Access a_bob ON s.ScreenID = a_bob.ScreenID AND a_bob.UserName = 'Bob'
-- 筛选Bob无权限或无记录的情况
WHERE (a_bob.HasAccess = 0 OR a_bob.ScreenID IS NULL);

如果需要先直观看到两位用户的所有屏幕权限对比，再筛选目标，可以用Pivot来生成透视表：

-- 先创建CTE生成权限透视表
WITH PermissionPivot AS (
    SELECT 
        s.ScreenName,
        -- 标记John是否有权限，无记录则视为0
        ISNULL(MAX(CASE WHEN a.UserName = 'John' THEN 1 ELSE 0 END), 0) AS John_HasAccess,
        -- 标记Bob是否有权限，无记录则视为0
        ISNULL(MAX(CASE WHEN a.UserName = 'Bob' THEN 1 ELSE 0 END), 0) AS Bob_HasAccess
    FROM Screen s
    LEFT JOIN Access a ON s.ScreenID = a.ScreenID
    GROUP BY s.ScreenName
)
-- 筛选John有、Bob没有的屏幕
SELECT ScreenName
FROM PermissionPivot
WHERE John_HasAccess = 1 AND Bob_HasAccess = 0;

你可以单独运行SELECT * FROM PermissionPivot，就能看到所有屏幕的权限对比表，方便整体查看两位用户的权限差异。

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