如何实现带分支的SQL条件查询？CASE/DECODE/IF/THEN选哪种？

原查询与需求 #

主查询代码 #

SELECT A.T_NBR
,A.I_NBR
,A.DATE
,A.MX_CD
,A.MY_CD
,A.S_CD
,B.O_NBR
FROM A,B,C,D,E,F,H,I,TMP_J
WHERE A.MY_CD IN ('FOO','BAR')
AND A.T_NBR NOT IN (
   SELECT A.T_NBR
   FROM A,K
   WHERE A.MY_CD = 'BAR'
   AND P_DATE < ADD_MONTHS(SYSDATE, -36)
   AND A.S_CD NOT IN ('AAA','BBB')
   AND K.K_CD NOT IN ('DDD','EEE')
   --JOIN
   AND A.I_NBR = K.I_NBR(+)
)
AND U_CD = 'FFFF'
--DATE LESS THAN 3 YEARS
AND TO_DATE(H.DATE,'YYYY-MM-DD') < ADD_MONTHS(SYSDATE, -36)
--NUM GREATER THAN 23 
AND TO_CHAR(SYSDATE,'SYYYY')-TO_CHAR(I.NUM,'SYYYY') > 23
AND A.S_CD NOT IN ('AAA','BBB')
AND B.DATE = (
   SELECT MAX(DATE)
   FROM B
)
AND B.O_NBR < 200
AND F.MG_CD <> '000'
AND A.T_NBR NOT IN (12345,54321,98765)
AND ((E.T IS NULL) OR (RTRIM(E.T) LIKE '%###%'))
AND TMP_J.I_NBR IS NULL
--JOINS
AND A.T_BR = B.T_NBR(+)
AND ((B.I_NBR = F.I_NBR) AND (B.DATE = F.DATE))
AND A.S_NBR = D.S_NBR(+)
AND A.D_NBR = C.D_NBR(+)
AND ((D.S_NBR = G.S_NBR(+)) AND (UPPER(G.A_CD(+)) = 'XXX'))
AND G.A_NBR = E.A_NBR(+)
AND H.I_NBR = A.I_NBR
AND A.S_NBR = I.S_NBR(+)
AND A.T_NBR = TMP_J.T_NBR(+);

分支需求 #

• 当A.MY_CD = 'BAR'时，仅应用以下条件：

A.T_NBR NOT IN (
   SELECT A.T_NBR
   FROM A,K
   WHERE A.MY_CD = 'BAR'
   AND P_DATE < ADD_MONTHS(SYSDATE, -36)
   AND A.S_CD NOT IN ('AAA','BBB')
   AND K.K_CD NOT IN ('DDD','EEE')
   AND A.I_NBR = K.I_NBR(+)
)

• 当A.MY_CD = 'FOO'时，仅应用以下条件：

U_CD = 'FFFF'
AND TO_DATE(H.DATE,'YYYY-MM-DD') < ADD_MONTHS(SYSDATE, -36)
AND TO_CHAR(SYSDATE,'SYYYY')-TO_CHAR(I.NUM,'SYYYY') > 23

• 其余WHERE子句保持不变，始终生效。

解决方案 #

不需要使用CASE/WHEN、DECODE或PL/SQL的IF/THEN，直接用SQL的逻辑运算符（AND/OR）分组实现分支即可，这种方式更直观且符合纯查询语句的写法。

修改后的完整查询 #

SELECT A.T_NBR
,A.I_NBR
,A.DATE
,A.MX_CD
,A.MY_CD
,A.S_CD
,B.O_NBR
FROM A,B,C,D,E,F,H,I,TMP_J
WHERE A.MY_CD IN ('FOO','BAR')
-- 核心分支逻辑
AND (
    -- MY_CD为BAR时的条件组
    (A.MY_CD = 'BAR' AND A.T_NBR NOT IN (
        SELECT A.T_NBR
        FROM A,K
        WHERE A.MY_CD = 'BAR'
        AND P_DATE < ADD_MONTHS(SYSDATE, -36)
        AND A.S_CD NOT IN ('AAA','BBB')
        AND K.K_CD NOT IN ('DDD','EEE')
        AND A.I_NBR = K.I_NBR(+)
    ))
    OR
    -- MY_CD为FOO时的条件组
    (A.MY_CD = 'FOO' 
        AND U_CD = 'FFFF'
        AND TO_DATE(H.DATE,'YYYY-MM-DD') < ADD_MONTHS(SYSDATE, -36)
        AND TO_CHAR(SYSDATE,'SYYYY')-TO_CHAR(I.NUM,'SYYYY') > 23)
)
-- 以下为始终生效的通用条件
AND A.S_CD NOT IN ('AAA','BBB')
AND B.DATE = (
   SELECT MAX(DATE)
   FROM B
)
AND B.O_NBR < 200
AND F.MG_CD <> '000'
AND A.T_NBR NOT IN (12345,54321,98765)
AND ((E.T IS NULL) OR (RTRIM(E.T) LIKE '%###%'))
AND TMP_J.I_NBR IS NULL
--JOINS
AND A.T_BR = B.T_NBR(+)
AND ((B.I_NBR = F.I_NBR) AND (B.DATE = F.DATE))
AND A.S_NBR = D.S_NBR(+)
AND A.D_NBR = C.D_NBR(+)
AND ((D.S_NBR = G.S_NBR(+)) AND (UPPER(G.A_CD(+)) = 'XXX'))
AND G.A_NBR = E.A_NBR(+)
AND H.I_NBR = A.I_NBR
AND A.S_NBR = I.S_NBR(+)
AND A.T_NBR = TMP_J.T_NBR(+);

逻辑说明 #

• 将分支条件用括号分组，通过OR连接两个分支场景，确保每条数据只会匹配其中一个分支的条件
• 通用条件直接保留在WHERE子句中，确保所有数据都必须满足这些规则
• 这种写法避免了嵌套复杂的CASE表达式，可读性更强，也不需要依赖PL/SQL语法

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