编写SQL查询：按ID判断所有STOCK为空时标记READY列为Y

Got it, let's fix this problem for you! The issue with your current CASE statement is that it only checks the individual row's STOCK value, not all rows belonging to the same ID. To mark every row of an ID as 'Y' in the READY column only if every STOCK entry for that ID is empty, we need to first evaluate the entire group of rows for each ID, then apply that result to all rows in the group.

Option 1: Using Window Functions (Most Efficient for Modern Databases) #

Window functions let us calculate values across a group of related rows without grouping the entire result set. Here are two clean approaches using this method:

Approach A: Check for Non-Empty STOCK with MAX

SELECT 
    ID,
    STOCK,
    CASE 
        -- If there are NO non-empty STOCK values in the ID group, mark 'Y'
        WHEN MAX(CASE WHEN STOCK <> '' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 0 
        THEN 'Y' 
        ELSE '' 
    END AS READY
FROM YOUR_TABLE;

• How it works:
  • OVER (PARTITION BY ID) groups all rows by their ID.
  • The inner CASE returns 1 if a row has a non-empty STOCK, 0 otherwise.
  • MAX(...) checks if there's any non-empty STOCK in the ID group. If MAX is 0, all STOCK values are empty.

Approach B: Count Non-Empty STOCK Values

This is more intuitive if you prefer counting:

SELECT 
    ID,
    STOCK,
    CASE 
        -- If count of non-empty STOCK values is 0, mark 'Y'
        WHEN COUNT(NULLIF(STOCK, '')) OVER (PARTITION BY ID) = 0 
        THEN 'Y' 
        ELSE '' 
    END AS READY
FROM YOUR_TABLE;

• How it works:
  • NULLIF(STOCK, '') converts empty strings to NULL (since COUNT ignores NULL values).
  • COUNT(...) counts how many non-empty STOCK values exist for the ID. If the count is 0, all STOCK entries are empty.

Option 2: Using a Subquery and Join #

If your database doesn't support window functions (unlikely for most modern systems), you can precompute the status of each ID first, then join it back to the original table:

SELECT 
    t.ID,
    t.STOCK,
    CASE WHEN id_status.all_stock_empty = 1 THEN 'Y' ELSE '' END AS READY
FROM YOUR_TABLE t
INNER JOIN (
    -- Subquery to check each ID's STOCK status
    SELECT 
        ID,
        CASE WHEN COUNT(NULLIF(STOCK, '')) = 0 THEN 1 ELSE 0 END AS all_stock_empty
    FROM YOUR_TABLE
    GROUP BY ID
) id_status ON t.ID = id_status.ID;

• How it works:
  • The subquery groups rows by ID and checks if all STOCK values are empty.
  • We join this result back to the original table, so every row gets the status of its parent ID.

Why Your Original Code Didn't Work #

Your initial CASE statement only looks at the current row's STOCK value:

CASE WHEN STOCK = '' THEN 'Y' ELSE '' END AS [READY]

This means it will mark 'Y' only on rows where that specific STOCK is empty, not when all STOCKs for the ID are empty. The solutions above fix this by evaluating the entire ID group first.

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