如何在Access中比较两个字符串，查询访客访问属性存在差异的课程

背景 #

负责维护的虚拟学习环境用于供学生访问课程资料，每门课程有唯一Course ID，每学年会从历史课程模板复制全量内容与属性，生成带新ID的新课程。

故障现状 #

1211批次（当前学年）的课程未正确继承Guest Access属性，已引发业务故障。
新老课程ID规则示例如下：

现有条件 #

• 已将1201、1211批次的全量课程数据从Postgres导出为Excel，导入Access建立了past（往年1201批次）、current（当前1211批次）两张表
• 已尝试编写关联SQL，但现有写法无法精准匹配对应课程，且Access不支持正则功能

解决方案 #

方案1：批次号替换匹配（性能最优，推荐） #

如果确认课程ID中1201、1211仅作为批次号出现在固定位置，不会在ID其他字段出现，直接用替换函数做关联，再加权限不等的过滤条件即可：

SELECT 
    past.course_id AS 往年课程ID,
    past.allow_guest_access AS 往年访客权限,
    current.course_id AS 当前课程ID,
    current.allow_guest_access AS 当前访客权限
FROM [current]
INNER JOIN [past] 
    ON REPLACE(current.course_id, "1211", "1201") = past.course_id
WHERE past.allow_guest_access <> current.allow_guest_access;

方案2：前后缀拼接匹配（兼容性最强） #

如果批次号可能在ID其他位置出现，可以通过匹配批次号前后的固定内容做关联：

SELECT 
    past.course_id AS 往年课程ID,
    past.allow_guest_access AS 往年访客权限,
    current.course_id AS 当前课程ID,
    current.allow_guest_access AS 当前访客权限
FROM [current], [past]
WHERE 
    -- 匹配批次号之前的内容
    LEFT(past.course_id, INSTR(1, past.course_id, "-1201-")) = LEFT(current.course_id, INSTR(1, current.course_id, "-1211-"))
    -- 匹配批次号之后的内容
    AND RIGHT(past.course_id, LEN(past.course_id) - INSTR(1, past.course_id, "-1201-") + 5) = RIGHT(current.course_id, LEN(current.course_id) - INSTR(1, current.course_id, "-1211-") + 5)
    -- 过滤权限不一致的记录
    AND past.allow_guest_access <> current.allow_guest_access;

两种方案执行后即可直接导出所有权限配置异常的课程列表。

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