如何在SQL的CASE WHEN语句中去除ELSE返回的N/A行？

一、去除N/A行的方法 #

方法1：在WHERE子句中直接过滤 #

将原CASE表达式复制到WHERE条件中，筛选出结果不等于'N/A'的行：

SELECT 
    CASE
        WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for QA Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
        THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for QA Approval</font></b> to <b><font color = "green">Final Approved%')
        WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
        THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for Approval</font></b> to <b><font color = "green">Final Approved%')
        WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%In Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
        THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%In Approval</font></b> to <b><font color = "green">Final Approved%')
        ELSE 'N/A'
    END AS "Approval Date"
FROM 你的表名 c -- 替换成实际表名
WHERE 
    CASE
        WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for QA Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
        THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for QA Approval</font></b> to <b><font color = "green">Final Approved%')
        WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
        THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for Approval</font></b> to <b><font color = "green">Final Approved%')
        WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%In Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
        THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%In Approval</font></b> to <b><font color = "green">Final Approved%')
        ELSE 'N/A'
    END <> 'N/A'

方法2：用CTE/子查询先计算再过滤 #

先通过CTE计算出所有审批日期，再筛选掉'N/A'的行，代码更易读：

WITH ApprovalDateCTE AS (
    SELECT 
        CASE
            WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for QA Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
            THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for QA Approval</font></b> to <b><font color = "green">Final Approved%')
            WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
            THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%Ready for Approval</font></b> to <b><font color = "green">Final Approved%')
            WHEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%In Approval</font></b> to <b><font color = "green">Final Approved%') LIKE '%[0-9]%'
            THEN (SELECT Substring(cy.cy_user_07,Charindex('Final Approved',cy.cy_user_07)+25,10) FROM cycle CY WHERE cy.cy_cycle_id = c.cy_cycle_id AND cy.cy_user_07 LIKE '%In Approval</font></b> to <b><font color = "green">Final Approved%')
            ELSE 'N/A'
        END AS "Approval Date"
    FROM 你的表名 c -- 替换成实际表名
)
SELECT "Approval Date"
FROM ApprovalDateCTE
WHERE "Approval Date" <> 'N/A'

二、更优的实现方式 #

原代码重复执行了6次相同的子查询，性能低下且维护困难。可以通过JOIN合并关联逻辑，一次性筛选出符合条件的记录并提取日期，直接避免生成'N/A'行：

SELECT 
    SUBSTRING(cy.cy_user_07, CHARINDEX('Final Approved', cy.cy_user_07) + 25, 10) AS "Approval Date"
FROM 你的表名 c -- 替换成实际表名
JOIN cycle cy ON cy.cy_cycle_id = c.cy_cycle_id
WHERE 
    -- 合并三个状态转换的匹配条件
    (cy.cy_user_07 LIKE '%Ready for QA Approval</font></b> to <b><font color = "green">Final Approved%'
    OR cy.cy_user_07 LIKE '%Ready for Approval</font></b> to <b><font color = "green">Final Approved%'
    OR cy.cy_user_07 LIKE '%In Approval</font></b> to <b><font color = "green">Final Approved%')
    -- 确保提取的子串包含数字（即有效日期）
    AND SUBSTRING(cy.cy_user_07, CHARINDEX('Final Approved', cy.cy_user_07) + 25, 10) LIKE '%[0-9]%'

优化点说明： #

1. 避免重复子查询：用JOIN替代多次子查询，只执行一次关联和字符串提取操作，提升性能。
2. 逻辑更简洁：直接在WHERE中筛选符合条件的记录，结果自然只有有效审批日期，无需后续过滤N/A。
3. 易维护：修改状态匹配条件时只需调整一处，无需重复修改多次子查询。

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