MS Access员工角色变动月度报表SQL查询去重问题求助

问题背景 #

需构建MS Access查询生成员工月度档案/角色变动报表，通过tbl_mov表对比相邻月份数据，识别招聘、离职及角色变动人员。

表结构（tbl_mov） #

id | DATE | MOV | NAME | PROFILE
 4 | Feb  |  +  | Mark |   tech 
 2 | Feb  |  +  | Joe  |   legal
 3 | Feb  |  -  | Mark |   ict
 1 | Jan  |  +  | Carl |   legal

期望查询结果（二月对比一月） #

MOV | NAME | PROFILE | cngROLE | mov1.id | mov2.id
 +  | Mark |  tech   |  true   |    4    |    3
 -  | Mark |  ict    |  true   |    3    |    4
 -  | Joe  |  legal  |  false  |    2    |    2

实际错误结果 #

MOV | NAME | PROFILE | cngROLE | mov1.id | mov2.id
 +  | Mark |  tech   |   true  |    4    |    3
 -  | Mark |  ict    |   true  |    3    |    4
 +  | Mark |  tech   |   false |    4    |    4
 -  | Mark |  ict    |   false |    3    |    3
 -  |  Joe |  legal  |   false |    2    |    2

当前查询语句 #

SELECT [...], IIF((mov1.mov <> mov2.mov AND mov1.profile <> mov2.profile); TRUE; FALSE) AS cngROLE 
FROM tbl_mov AS mov1 
INNER JOIN tbl_mov AS mov2 ON (mov1.date = mov2.date AND mov1.name = mov2.name)

问题原因及修复方案 #

原因分析 #

当前自连接条件mov1.date = mov2.date AND mov1.name = mov2.name会将同一月份、同一姓名的所有记录两两匹配（包括记录自身），因此生成了多余的自连接重复行。

修复方案 #

要实现相邻月份跨月对比，需调整连接逻辑，只关联目标月份与上月的同姓名记录，并过滤无效的自连接行：

固定月份对比（二月对比一月）

SELECT 
    mov1.MOV,
    mov1.NAME,
    mov1.PROFILE,
    IIF(mov2.id IS NOT NULL AND mov1.mov <> mov2.mov AND mov1.PROFILE <> mov2.PROFILE, TRUE, FALSE) AS cngROLE,
    mov1.id AS [mov1.id],
    Nz(mov2.id, mov1.id) AS [mov2.id]
FROM 
    tbl_mov AS mov1
LEFT JOIN 
    tbl_mov AS mov2 
ON 
    mov1.NAME = mov2.NAME 
    AND mov1.DATE = "Feb" 
    AND mov2.DATE = "Jan"
WHERE 
    mov1.DATE = "Feb"
    AND (
        -- 保留跨月角色变动的匹配记录
        (mov2.id IS NOT NULL AND mov1.mov <> mov2.mov AND mov1.PROFILE <> mov2.PROFILE)
        -- 保留本月新增/离职且无上月对应记录的情况
        OR mov2.id IS NULL
    )

通用跨月对比（自动匹配当前月与上月）

如果需要适配任意月份的对比，可通过日期函数自动关联上月数据：

SELECT 
    mov1.MOV,
    mov1.NAME,
    mov1.PROFILE,
    IIF(mov2.id IS NOT NULL AND mov1.mov <> mov2.mov AND mov1.PROFILE <> mov2.PROFILE, TRUE, FALSE) AS cngROLE,
    mov1.id AS [mov1.id],
    Nz(mov2.id, mov1.id) AS [mov2.id]
FROM 
    tbl_mov AS mov1
LEFT JOIN 
    tbl_mov AS mov2 
ON 
    mov1.NAME = mov2.NAME 
    AND DateAdd("m", -1, DateValue("1 " & mov1.DATE)) = DateValue("1 " & mov2.DATE)
WHERE 
    -- 可指定目标月份，例如"Feb"，留空则查询所有月份的变动
    mov1.DATE = "Feb"
    AND (
        (mov2.id IS NOT NULL AND mov1.mov <> mov2.mov AND mov1.PROFILE <> mov2.PROFILE)
        OR mov2.id IS NULL
    )

逻辑说明 #

1. 用LEFT JOIN替代INNER JOIN，确保本月新增/离职的员工记录不会被遗漏
2. 连接条件改为跨月同姓名匹配，避免同一月份的无效自连接
3. 通过WHERE过滤掉无意义的重复行，只保留跨月变动记录或本月独有记录
4. 用Nz(mov2.id, mov1.id)处理无上月对应记录的情况，让mov2.id默认等于mov1.id

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