SQL查询需求：若订单批次含特定状态则跳过对应订单

Hey there! Let's solve this problem where we need to pull order numbers and their corresponding statuses only if the order batch never entered a Z or B status at any stage. Here are a few solid approaches depending on your database system:

Method 1: Using NOT EXISTS Subquery (Most Widely Compatible) #

This is a straightforward, efficient approach that works across nearly all SQL databases. We check that no records exist for the same order with a Z or B status.

SELECT o.order_number, o.status
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.order_number = o.order_number
      AND o2.status IN ('Z', 'B')
);

Explanation: #

• The outer query fetches all order records.
• The subquery verifies if there's any entry for the same order with a Z or B status.
• If the subquery returns no results (meaning the order never had Z/B status), the outer row is included in the final output.

Method 2: Using GROUP BY + HAVING Clause #

If you prefer to first identify valid orders then retrieve their statuses, this method works well:

First, get the list of orders that never had Z/B status:

SELECT order_number
FROM orders
GROUP BY order_number
HAVING SUM(CASE WHEN status IN ('Z', 'B') THEN 1 ELSE 0 END) = 0;

Then join back to the original table to get all status records for those valid orders:

SELECT o.order_number, o.status
FROM orders o
JOIN (
    SELECT order_number
    FROM orders
    GROUP BY order_number
    HAVING SUM(CASE WHEN status IN ('Z', 'B') THEN 1 ELSE 0 END) = 0
) valid_orders ON o.order_number = valid_orders.order_number;

Explanation: #

• The subquery groups orders by their number and counts how many times Z/B statuses appear. A count of 0 means the order is valid.
• We join this valid order list back to the original table to fetch all associated status entries.

Method 3: Using Window Functions (For Modern Databases) #

If you're using PostgreSQL, MySQL 8+, SQL Server, or another database that supports window functions, this approach is clean and efficient:

WITH order_status_summary AS (
    SELECT 
        order_number,
        status,
        MAX(CASE WHEN status IN ('Z', 'B') THEN 1 ELSE 0 END) OVER (PARTITION BY order_number) AS has_z_or_b
    FROM orders
)
SELECT order_number, status
FROM order_status_summary
WHERE has_z_or_b = 0;

Explanation: #

• The CTE (Common Table Expression) calculates a flag (has_z_or_b) for each order, indicating if it ever had a Z or B status.
• We filter out all rows where this flag is 1, leaving only orders that never entered Z/B status.

Quick Notes: #

• Replace orders with your actual table name.
• Ensure order_number is the correct column that groups all records for a single order batch.
• All methods will fully exclude any order that has even one Z or B status in its history.

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