查询所有未在事件表中记录操作项的人员——NOT EXISTS复杂查询求助

我来帮你搞定这个问题！首先得明确核心需求：我们要找出每个人员在事件表中没有记录过的所有操作项。这里的关键是先确定「所有可能的操作项」——下面分两种常见情况来给出解决方案：

情况1：有单独的操作项表（比如actions） #

如果你的系统里有存储所有操作项的表（比如记录ActionID和对应的操作名称），先补全这个表的示例：

CREATE TABLE [dbo].[actions](
    [ActionID] [int] NOT NULL,
    [ActionName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

INSERT INTO actions (ActionID, ActionName) VALUES (1, '登录')
INSERT INTO actions (ActionID, ActionName) VALUES (2, '登出')
INSERT INTO actions (ActionID, ActionName) VALUES (3, '更新资料')

然后用以下查询即可得到结果，完全基于NOT EXISTS实现：

SELECT 
    p.PersonID,
    p.Name,
    a.ActionID,
    a.ActionName
FROM 
    dbo.people p
CROSS JOIN 
    dbo.actions a
WHERE 
    NOT EXISTS (
        -- 检查当前人员+操作项的组合是否在事件表中存在
        SELECT 1
        FROM dbo.events e
        WHERE e.PersonID = p.PersonID
          AND e.ActionID = a.ActionID
    )
ORDER BY 
    p.PersonID, a.ActionID;

逻辑解释： #

1. CROSS JOIN会生成「所有人员 × 所有操作项」的全量组合，这是我们的基础候选集
2. NOT EXISTS子句会过滤掉那些已经在events表中有记录的组合，剩下的就是每个人员未执行过的操作项

情况2：没有单独的操作项表，ActionID是固定范围 #

如果你的操作项是固定的几个值（比如ActionID只能是1、2、3），可以用VALUES子句临时生成操作项列表，不需要额外建表：

SELECT 
    p.PersonID,
    p.Name,
    a.ActionID
FROM 
    dbo.people p
CROSS JOIN 
    (VALUES (1), (2), (3)) AS a(ActionID)
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM dbo.events e
        WHERE e.PersonID = p.PersonID
          AND e.ActionID = a.ActionID
    )
ORDER BY 
    p.PersonID, a.ActionID;

优化建议 #

为了让这个查询跑得更快，建议在events表上创建联合索引：

CREATE NONCLUSTERED INDEX IX_events_PersonID_ActionID 
ON dbo.events(PersonID, ActionID);

这个索引可以让NOT EXISTS的子查询快速定位到是否存在匹配记录，避免全表扫描。

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