如何在SQL查询中排除含指定取消状态码的order_id+item_id组合

原SQL查询用于获取未发货（ship_date = 0）且date_in晚于2024-01-01的订单状态更新记录，但存在问题：部分order_id + item_id组合包含状态码140、141的取消记录，这些组合的所有记录都需要被彻底排除。

原查询代码：

SELECT
    oi.order_id, oi.item_id, oi.item_description, ois.status_code, s.status_name, ois.status_date,
    FROM_UNIXTIME(ois.status_date) AS status_date_readable, ois.employee_code, ois.qc_fail, ois.`comment`
FROM
    order_item AS oi
INNER JOIN order_item_status AS ois ON oi.order_id = ois.order_id
INNER JOIN `status` AS s ON ois.status_code = s.status_code
WHERE
    oi.ship_date = 0 AND oi.date_in > UNIX_TIMESTAMP('2024-01-01 00:00:00')
ORDER BY oi.order_id ASC, oi.item_id ASC, ois.status_date ASC;

实现方法 #

方法一：使用NOT EXISTS子查询

通过子查询检查当前order_id+item_id是否存在取消状态记录，若存在则排除整个组合：

SELECT
    oi.order_id, oi.item_id, oi.item_description, ois.status_code, s.status_name, ois.status_date,
    FROM_UNIXTIME(ois.status_date) AS status_date_readable, ois.employee_code, ois.qc_fail, ois.`comment`
FROM
    order_item AS oi
INNER JOIN order_item_status AS ois ON oi.order_id = ois.order_id AND oi.item_id = ois.item_id
INNER JOIN `status` AS s ON ois.status_code = s.status_code
WHERE
    oi.ship_date = 0 
    AND oi.date_in > UNIX_TIMESTAMP('2024-01-01 00:00:00')
    AND NOT EXISTS (
        SELECT 1
        FROM order_item_status ois_cancel
        WHERE ois_cancel.order_id = oi.order_id
          AND ois_cancel.item_id = oi.item_id
          AND ois_cancel.status_code IN (140, 141)
    )
ORDER BY oi.order_id ASC, oi.item_id ASC, ois.status_date ASC;

注：原查询的order_item与order_item_status关联仅用了order_id，补充item_id关联更严谨，避免错误匹配。

方法二：LEFT JOIN+过滤

通过左连接取消状态记录，筛选出无取消记录的组合：

SELECT
    oi.order_id, oi.item_id, oi.item_description, ois.status_code, s.status_name, ois.status_date,
    FROM_UNIXTIME(ois.status_date) AS status_date_readable, ois.employee_code, ois.qc_fail, ois.`comment`
FROM
    order_item AS oi
INNER JOIN order_item_status AS ois ON oi.order_id = ois.order_id AND oi.item_id = ois.item_id
INNER JOIN `status` AS s ON ois.status_code = s.status_code
LEFT JOIN order_item_status ois_cancel 
    ON ois_cancel.order_id = oi.order_id 
    AND ois_cancel.item_id = oi.item_id 
    AND ois_cancel.status_code IN (140, 141)
WHERE
    oi.ship_date = 0 
    AND oi.date_in > UNIX_TIMESTAMP('2024-01-01 00:00:00')
    AND ois_cancel.order_id IS NULL
ORDER BY oi.order_id ASC, oi.item_id ASC, ois.status_date ASC;

方法三：GROUP BY+HAVING预筛选合法组合

先筛选出无取消状态的order_id+item_id组合，再关联查询：

SELECT
    oi.order_id, oi.item_id, oi.item_description, ois.status_code, s.status_name, ois.status_date,
    FROM_UNIXTIME(ois.status_date) AS status_date_readable, ois.employee_code, ois.qc_fail, ois.`comment`
FROM
    order_item AS oi
INNER JOIN order_item_status AS ois ON oi.order_id = ois.order_id AND oi.item_id = ois.item_id
INNER JOIN `status` AS s ON ois.status_code = s.status_code
INNER JOIN (
    SELECT order_id, item_id
    FROM order_item_status
    GROUP BY order_id, item_id
    HAVING SUM(CASE WHEN status_code IN (140,141) THEN 1 ELSE 0 END) = 0
) valid_combinations ON oi.order_id = valid_combinations.order_id AND oi.item_id = valid_combinations.item_id
WHERE
    oi.ship_date = 0 
    AND oi.date_in > UNIX_TIMESTAMP('2024-01-01 00:00:00')
ORDER BY oi.order_id ASC, oi.item_id ASC, ois.status_date ASC;

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