Oracle实现gr.schoolname与PSSDPriorSchools代码集部分字符串匹配

需求描述 #

需要将storedgrades表中gr.schoolname字段（仅包含纯学校名称），与codeset表（编码类型为PSSD_PriorSchools）的c.displayvalue字段（格式为「学校名称 (学区名称)」）进行前缀匹配，从而获取对应的c.code编码。

原SQL问题 #

原SQL使用完全匹配条件gr.schoolname = C.displayvalue，无法适配前缀匹配场景（例如gr.schoolname为Pilot Mound School，而c.displayvalue为Pilot Mound School (PRAIRIE SPIRIT SCHOOL DIVISION)），且尝试的decode改写未生效。

原SQL代码：

Select
s.ID as ID,s.LASTFIRST as LASTFIRST,s.STUDENT_NUMBER as STUDENT_NUMBER,
decode(upper(gr.schoolname), c.code, c.displayvalue) as credit_schools
from 
                     STUDENTS s,
                     codeset c,
                     storedgrades gr
where
                     gr.schoolname= C.displayvalue
                     and c.codetype = 'PSSD_PriorSchools'
                     and gr.studentid=s.id

数据示例 #

codeset表（PSSD_PriorSchools类型） #

storedgrades表schoolname字段 #

正确实现方案 #

方案1：使用LIKE前缀匹配（简洁高效） #

适合确定c.displayvalue后缀统一为 (学区名称)格式的场景：

SELECT
    s.ID AS ID,
    s.LASTFIRST AS LASTFIRST,
    s.STUDENT_NUMBER AS STUDENT_NUMBER,
    c.code AS credit_school_code,
    c.displayvalue AS credit_school_full_name
FROM STUDENTS s
JOIN storedgrades gr ON gr.studentid = s.id
JOIN codeset c 
    ON c.codetype = 'PSSD_PriorSchools'
    AND c.displayvalue LIKE gr.schoolname || ' (%)'

方案2：截取字符串匹配（严谨兼容） #

通过截取c.displayvalue中括号前的部分，与gr.schoolname做精确匹配，同时兼容无括号的特殊情况：

SELECT
    s.ID AS ID,
    s.LASTFIRST AS LASTFIRST,
    s.STUDENT_NUMBER AS STUDENT_NUMBER,
    c.code AS credit_school_code,
    c.displayvalue AS credit_school_full_name
FROM STUDENTS s
JOIN storedgrades gr ON gr.studentid = s.id
JOIN codeset c 
    ON c.codetype = 'PSSD_PriorSchools'
    AND (
        -- 处理带括号的常规场景，用TRIM消除括号前的多余空格
        (INSTR(c.displayvalue, '(') > 0 AND TRIM(SUBSTR(c.displayvalue, 1, INSTR(c.displayvalue, '(') - 1)) = gr.schoolname)
        -- 兼容无括号的特殊情况
        OR (INSTR(c.displayvalue, '(') = 0 AND c.displayvalue = gr.schoolname)
    )

说明 #

• 方案1利用LIKE的通配符特性，直接匹配以gr.schoolname开头且后跟 (任意内容)的记录，写法简洁。
• 方案2通过字符串截取和TRIM处理，能避免因c.displayvalue中括号前存在多余空格导致的匹配失败，同时兼容无括号的异常数据，更严谨。

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