筛选仅含LOBA交易码的代理数据SQL查询修正需求

需求说明 #

需要从输入表中筛选出仅存在LOBA交易码、无LARA交易码的代理对应的LOBA交易行（期望返回2行），但当前执行的SQL返回了所有LOBA交易行，需修正查询逻辑。

输入表 #

agent_id  transaction_code date       state_code
12345233      LARA         20230509      FL
12345233      LOBA         20230509      FL
45678342      LARA         20230509      AL
45678342      LOBA         20230509      AL
45678342      LOBA         20230509      AL
68939393      LOBA         20230509      AL
74953738      LOBA         20230509      GA
68939393      LARA         20230509      FL
68939393      LOBA         20230509      FL
68939393      LOBA         20230509      FL

期望输出 #

agent_id  transaction_code date       state_code
68939393      LOBA         20230509     AL
74953738      LOBA         20230509     GA

当前错误输出 #

agent_id  transaction_code date       state_code
12345233      LOBA         20230509      FL
45678342      LOBA         20230509      AL
45678342      LOBA         20230509      AL
68939393      LOBA         20230509      AL
74953738      LOBA         20230509      GA
68939393      LOBA         20230509      FL
68939393      LOBA         20230509      FL

当前执行的SQL #

SELECT DISTINCT SUBSTR(A.RECORD_KEY,1,10) AS "PRODUCER_TAX_ID",
CASE WHEN B.SEX_CODE = 'E'THEN TRIM(B.CORPORATE_NAME) ELSE TRIM(B.FIRST_NAME) || TRIM(B.MIDDLE_NAME) || TRIM(B.LAST_NAME) END AS "PRODUCER_NAME",
SUBSTR(A.RECORD_KEY,11,2) AS "STATE_CODE", SUBSTR(A.RECORD_AREA,29,8) AS "APPOINTMENT_EFFECTIVE_DATE", A.ORIGINATOR_CD AS "USER_ID",
DECODE(A.FILE_MAINT_TRX_CD ,'LOBA','ADD') AS "TRANSACTION_TYPE",
SUBSTR(A.RECORD_AREA,134,1) AS "SEND_TO_STATE", A.LOG_DT_R AS "LAST_CHANGED_DT" , A.LOG_TIME AS "TIME_PROCESSED" 
FROM PPL_S01.REG_HIST A , PPL_S01.MPR B
WHERE SUBSTR(A.RECORD_KEY,1,10) NOT IN (select DISTINCT SUBSTR(A.RECORD_KEY,1,10)
from PPL_S01.REG_HIST R
where A.FILE_MAINT_TRX_CD = 'LARA' AND SUBSTR(R.RECORD_KEY,1,10) = SUBSTR(A.RECORD_KEY,1,10))
AND A.FILE_MAINT_TRX_CD = 'LOBA' 
AND B.MSTR_AGENT_ID = SUBSTR(A.RECORD_KEY,1,10)

问题分析 #

当前SQL的核心错误在子查询逻辑：子查询里写了A.FILE_MAINT_TRX_CD = 'LARA'，但外层已经把A表的交易码限定为LOBA了，这就导致子查询根本查不到任何数据，NOT IN自然永远成立，所以会返回所有LOBA交易行。

修正后的SQL #

SELECT DISTINCT SUBSTR(A.RECORD_KEY,1,10) AS "PRODUCER_TAX_ID",
CASE WHEN B.SEX_CODE = 'E' THEN TRIM(B.CORPORATE_NAME) ELSE TRIM(B.FIRST_NAME) || TRIM(B.MIDDLE_NAME) || TRIM(B.LAST_NAME) END AS "PRODUCER_NAME",
SUBSTR(A.RECORD_KEY,11,2) AS "STATE_CODE", SUBSTR(A.RECORD_AREA,29,8) AS "APPOINTMENT_EFFECTIVE_DATE", A.ORIGINATOR_CD AS "USER_ID",
DECODE(A.FILE_MAINT_TRX_CD ,'LOBA','ADD') AS "TRANSACTION_TYPE",
SUBSTR(A.RECORD_AREA,134,1) AS "SEND_TO_STATE", A.LOG_DT_R AS "LAST_CHANGED_DT" , A.LOG_TIME AS "TIME_PROCESSED" 
FROM PPL_S01.REG_HIST A 
JOIN PPL_S01.MPR B ON B.MSTR_AGENT_ID = SUBSTR(A.RECORD_KEY,1,10)
WHERE SUBSTR(A.RECORD_KEY,1,10) NOT IN (
    SELECT DISTINCT SUBSTR(R.RECORD_KEY,1,10)
    FROM PPL_S01.REG_HIST R
    WHERE R.FILE_MAINT_TRX_CD = 'LARA'
)
AND A.FILE_MAINT_TRX_CD = 'LOBA'

修正说明 #

1. 子查询调整为直接从R表中找出所有交易码是LARA的代理ID，不再关联外层A表的条件，这样能准确拿到所有有过LARA交易的代理集合。
2. 把原来的隐式连接改成显式JOIN，让SQL逻辑更清晰易懂。
3. 外层查询用这个代理集合做排除，只保留那些从来没有过LARA交易的代理的LOBA记录，完全符合需求。

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