用子查询替代CASE WHEN以避免“WHEN子句非法表达式”错误

Hey there! Let's work through this problem step by step to get your query working correctly.

Just to make sure we're aligned: you need to count, per country and item type, the number of repeat customers who first placed an Order_size = Big order, then later placed an order where order_size <> Big. Your original query handled this, but adding a subquery with specific table conditions inside a CASE WHEN clause caused errors.

When subqueries inside CASE WHEN throw errors, it's almost always one of these issues:

• The subquery returns multiple rows (CASE WHEN expects a single scalar value)
• Ambiguous field references (e.g., same column name in multiple tables without aliases)
• Incorrect join logic in the subquery leading to unexpected results

Instead of cramming complex subqueries into CASE WHEN, use Common Table Expressions (CTEs) to break the logic into manageable steps. This makes the query easier to debug and modify, plus it avoids the scalar value issue.

Assuming your table is named orders with columns country, item_type, customer_id, order_size, and order_date (we need dates to confirm the order of purchases), here's the corrected query:

-- Step 1: Get the first Big order date for each customer, per country + item type
WITH customer_first_big AS (
    SELECT
        country,
        item_type,
        customer_id,
        MIN(order_date) AS first_big_date
    FROM orders
    WHERE order_size = 'Big'
        -- Add your specific table conditions here (e.g., AND status = 'Completed')
    GROUP BY country, item_type, customer_id
),
-- Step 2: Identify customers who placed non-Big orders AFTER their first Big order
qualified_customers AS (
    SELECT DISTINCT
        c.country,
        c.item_type,
        c.customer_id
    FROM customer_first_big c
    JOIN orders o
        ON c.country = o.country
        AND c.item_type = o.item_type
        AND c.customer_id = o.customer_id
        AND o.order_date > c.first_big_date
        AND o.order_size <> 'Big'
        -- Add any specific conditions for the non-Big orders here
)
-- Step 3: Count the unique qualified customers per country + item type
SELECT
    country,
    item_type,
    COUNT(customer_id) AS repeat_customer_count
FROM qualified_customers
GROUP BY country, item_type
ORDER BY country, item_type;

• The first CTE isolates all customers who have placed a Big order, capturing their earliest Big order date. You can directly add your specific table conditions here without breaking the logic.
• The second CTE joins back to the orders table to find non-Big orders that happened after the customer's first Big order, ensuring we only count customers who meet the sequence requirement.
• Using DISTINCT ensures we don't count the same customer multiple times if they placed multiple non-Big orders after their Big order.

If you need to stick with a CASE WHEN approach, use EXISTS instead of a subquery that returns values—EXISTS returns a boolean (true/false), which plays nicely with CASE WHEN. Here's how to adjust it:

SELECT
    country,
    item_type,
    COUNT(DISTINCT
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM orders o2
                WHERE o2.customer_id = o1.customer_id
                    AND o2.country = o1.country
                    AND o2.item_type = o1.item_type
                    AND o2.order_size = 'Big'
                    AND o2.order_date < o1.order_date
                    -- Add your specific conditions for the Big order here
            )
            AND o1.order_size <> 'Big'
            -- Add your specific conditions for the non-Big order here
            THEN o1.customer_id
            ELSE NULL
        END
    ) AS repeat_customer_count
FROM orders o1
GROUP BY country, item_type
ORDER BY country, item_type;

This avoids the "multiple rows returned" error because EXISTS only checks for the presence of a matching row, not returns its values.

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