PostgreSQL查询需求：主疾病码无匹配时查询关联疾病码

先明确核心业务逻辑：我们需要处理两种匹配场景——主码直接匹配、关联码映射后主码匹配，再根据需求筛选对应记录。以下是针对不同需求的修正方案：

需求1：筛选主码、关联码均无匹配的记录 #

即病例既无法通过c_oplmp直接匹配lic_app_new.c_serv，也无法通过ksg_p的关联码映射找到匹配的lic_app_new记录。

select distinct 
    ocf.code_head_mo,
    cs.c_oplmp,
    r."name",
    ocf.code_state,
    rm2.name_short,
    ocf.id_condition_mc 
from puomp.op_registry r 
join puomp.op_case_finish ocf on r.id_registry = ocf.id_registry 
join puomp.op_patient pt on ocf.id_patient = pt.id_patient 
join puomp.op_case sl on ocf.id_case_finish = sl.id_case_finish 
join puomp.op_case_ext cs on sl.id_case = cs.id_case
left join nsi.ref_mo rm2 on r.id_head_mo = rm2.id_mo 
where 
    -- 主码无直接匹配
    not exists (
        select 1 
        from lic_app_new lm
        where lm.fc_mo = ocf.code_head_mo 
          and lm.c_serv = cs.c_oplmp
    )
    -- 关联码映射后主码也无匹配
    and not exists (
        select 1 
        from lic_app_new lm
        join ksg_p kp on lm.c_serv = kp.c_prof
        where lm.fc_mo = ocf.code_head_mo 
          and kp.smj_prof = cs.c_oplmp
    );

需求2：筛选主码无匹配但关联码有匹配的记录 #

即病例主码匹配失败，但通过ksg_p关联码映射到主码后，能找到对应的lic_app_new记录。

select distinct 
    ocf.code_head_mo,
    cs.c_oplmp,
    r."name",
    ocf.code_state,
    rm2.name_short,
    ocf.id_condition_mc 
from puomp.op_registry r 
join puomp.op_case_finish ocf on r.id_registry = ocf.id_registry 
join puomp.op_patient pt on ocf.id_patient = pt.id_patient 
join puomp.op_case sl on ocf.id_case_finish = sl.id_case_finish 
join puomp.op_case_ext cs on sl.id_case = cs.id_case
left join nsi.ref_mo rm2 on r.id_head_mo = rm2.id_mo 
where 
    -- 主码无直接匹配
    not exists (
        select 1 
        from lic_app_new lm
        where lm.fc_mo = ocf.code_head_mo 
          and lm.c_serv = cs.c_oplmp
    )
    -- 但关联码映射后主码有匹配
    and exists (
        select 1 
        from lic_app_new lm
        join ksg_p kp on lm.c_serv = kp.c_prof
        where lm.fc_mo = ocf.code_head_mo 
          and kp.smj_prof = cs.c_oplmp
    );

关键修正点 #

1. 移除原查询中不符合PostgreSQL语法的if...endif语句，改用标准exists/not exists子句实现分支逻辑
2. 给ksg_p表添加合法关联条件，保证表关联逻辑的正确性
3. 子查询使用select 1替代select *，减少不必要的数据扫描，提升查询性能

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