Oracle SQL使用CASE语句查询三类无效订单的实现方案

实现思路 #

三个无效判定条件互相独立，同一订单可能同时满足多个条件，推荐使用UNION ALL拼接三个条件的查询结果，确保不会漏匹配任何无效场景。

完整SQL代码 #

-- 条件1：支付类型为货到付款
SELECT 
    cus_no,
    order_num,
    'This order does not qualify for CoD' AS comments
FROM orders
WHERE pay_type = 'Cash on Delivery'

UNION ALL

-- 条件2：MFC字段非空
SELECT 
    cus_no,
    order_num,
    'This order can not be an MFC' AS comments
FROM orders
WHERE MFC IS NOT NULL

UNION ALL

-- 条件3：商品编号不在有效期内的允许售卖列表中
SELECT 
    cus_no,
    order_num,
    'Non listed items can not be ordered' AS comments
FROM orders
WHERE prod_no NOT IN (
    SELECT field_value
    FROM config_check
    WHERE check_type = 'Invalid Orders Check'
      AND sysdate BETWEEN start_date AND end_date
      AND field_value IS NOT NULL -- 避免空值导致NOT IN匹配异常
)

验证说明 #

测试数据中Invalid Orders Check类型的配置有效期均为2021年，若当前系统时间已超出该范围，第三个条件的子查询会返回空列表，所有订单都会命中第三个条件。如果要验证prod_no=1110的订单匹配效果，可将第三个条件中的sysdate替换为DATE'2021-11-20'，即可看到该订单正常返回。

单条返回适配 #

如果要求每个订单仅返回一条记录，多个条件同时满足时按优先级取最先匹配的结果，可使用CASE WHEN写法：

SELECT 
    cus_no,
    order_num,
    CASE 
        WHEN pay_type = 'Cash on Delivery' THEN 'This order does not qualify for CoD'
        WHEN MFC IS NOT NULL THEN 'This order can not be an MFC'
        WHEN prod_no NOT IN (
            SELECT field_value
            FROM config_check
            WHERE check_type = 'Invalid Orders Check'
              AND sysdate BETWEEN start_date AND end_date
              AND field_value IS NOT NULL
        ) THEN 'Non listed items can not be ordered'
    END AS comments
FROM orders
WHERE 
    pay_type = 'Cash on Delivery'
    OR MFC IS NOT NULL
    OR prod_no NOT IN (
        SELECT field_value
        FROM config_check
        WHERE check_type = 'Invalid Orders Check'
          AND sysdate BETWEEN start_date AND end_date
          AND field_value IS NOT NULL
    )

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