多表关联查询求助：基于cmr表筛选规则关联active表获取指定数据

我有两张数据表，结构及数据如下：

active表 #

orderno status  state name
204     N       0   DANDO A SIN_CONDICION
190     N       L   JOB_AGENTE_SLEEP_200M
203     N       0   SIN_CONDICION
206     N       C   EJEMPLO_2
182     N       L   JOB_AGENTE
195     Y       8   BIENHECHO
186     N       L   JOB_AGENTE_SLEEP_20M
189     N       L   JOB_AGENTE_SLEEP_5S
205     Y       8   EJEMPLO_1
184     N       L   SSL_AGENTE920
183     N       L   LIFECONTROLM_JOB1_920
191     N       L   MFTAGENTEPRIMERO
187     N       L   SSL_AGENTE931
198     N       B   PTE_HORA_Y_CONDICION
194     N       L   MFTAGENTESECUNDARIO
201     N       B   COND2
197     N       B   PTE_HORA
192     N       L   931a920
185     N       L   LS_AGENTE920_OTRA_EJECUCION
188     N       L   AGENTE921
200     Y       8   COND1
196     Y       8   BIENHECHO2
193     N       C   SIN_CONDICION
202     Y       8   DANDO A SIN_CONDICION
199     Y       8   SIN_CONDICION

cmr表 #

cond                            orderno rowtype date    op  
SIN_CONDICION                   193     I       1107    A   
COND1-TO-SIN_CONDICION          193     I       1107    A         
COND2-TO-SIN_CONDICION          193     I       1107    A   
COND1-TO-SIN_CONDICION          193     O       1107    -         
COND2-TO-SIN_CONDICION          193     O       1107    -         
BIENHECHO-TO-BIENHECHO2         195     O       1107    +         
BIENHECHO-TO-BIENHECHO2         196     I       1107    A     
BIENHECHO-TO-BIENHECHO2         196     O       1107    -         
PTE_HORA_Y_CONDICION            198     I       1107    A         
SIN_CONDICION                   199     I       1107    A         
SIN_CONDICION                   199     O       1107    -     
COND1-TO-PTE_HORA_Y_CONDICION   200     O       1107    +         
COND1-TO-SIN_CONDICION          200     O       1107    +         
COND2-TO-SIN_CONDICION          201     O       1107    +     
SIN_CONDICION                   202     O       1107    +         
SIN_CONDICION                   203     I       1107    A         
SIN_CONDICION2                  203     I       1107    A     
SIN_CONDICION2                  203     O       1107    -     
SIN_CONDICION                   204     O       1107    +         
EJEMPLO_1-TO-EJEMPLO_2          205     O       1107    +         
EJEMPLO_1-TO-EJEMPLO_2          206     I       1107    A         
CONDICION3                      206     I       1107    A         
EJEMPLO_1-TO-EJEMPLO_2          206     O       1107    -         

查询要求 #

需要从cmr表筛选满足以下条件的行：

• 该行的rowtype为I且op为A；
• 不存在同一cond、同一date下，rowtype为O且op为+的行。

之后通过这些行的orderno关联active表，获取对应的name字段。

示例说明 #

CONDICION3存在rowtype:I且op:A、date为1107的行，且不存在同一cond、date下rowtype:O且op:+的行
符合要求，关联后得到name为EJEMPLO_2。

EJEMPLO_1-TO-EJEMPLO_2存在rowtype:I且op:A、date为1107的行，同时存在同一cond、date下rowtype:O且op:+的行
不符合要求，不纳入结果。

解决方案 #

可以使用NOT EXISTS子查询实现条件判断，再关联active表获取目标字段：

SELECT a.name
FROM cmr c
JOIN active a ON c.orderno = a.orderno
WHERE c.rowtype = 'I' 
  AND c.op = 'A'
  AND NOT EXISTS (
    SELECT 1
    FROM cmr c2
    WHERE c2.cond = c.cond
      AND c2.date = c.date
      AND c2.rowtype = 'O'
      AND c2.op = '+'
  );

逻辑解释 #

1. 先从cmr表筛选出rowtype='I'且op='A'的基础数据集；
2. 通过NOT EXISTS子查询校验：确保当前行的cond和date组合下，没有匹配rowtype='O'且op='+'的记录；
3. 将符合条件的cmr记录与active表通过orderno关联，提取对应的name字段。

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