SQL多条件求和问询：含code筛选的qtyRemaining字段计算

Hey there! Let's tackle this qtyRemaining calculation problem step by step based on your requirements and sample data.

First, let's lock in the rules we need to enforce:

• For records where code IN (25, 26), we only sum up positive qty values (ignoring zeros and negatives) within the same ref_x and code group.
• For records where code = 27, we need the total sum of all qty values from code = 26 records under the same ref_x.

Working SQL Query #

Here's a clean query using window functions to handle these scenarios exactly as needed:

SELECT 
    ref_x,
    qty,
    code,
    CASE 
        WHEN code IN (25, 26) THEN 
            SUM(CASE WHEN qty > 0 THEN qty ELSE 0 END) OVER (PARTITION BY ref_x, code)
        WHEN code = 27 THEN 
            SUM(qty) OVER (PARTITION BY ref_x) FILTER (WHERE code = 26)
        ELSE 0 -- Adjust this default if you need to handle other codes differently
    END AS qtyRemaining
FROM your_table_name;

Breakdown of the Logic #

Let's walk through how this matches your sample results:

• Code 25/26 Handling: The inner CASE statement only includes qty values greater than 0 in the sum. We partition by ref_x and code to ensure we're only calculating within the same reference and code group. That's why your code 25 records (both qty 0) and code 26 records (both negative) end up with qtyRemaining = 0.
• Code 27 Handling: The FILTER clause targets only code 26 records in the same ref_x group, summing all their qty values (including negatives). This gives the -200 result you see in the sample for code 27 rows.
• Fallback for Other Codes: We set a default of 0, but feel free to tweak this if you have other code values that need special handling.

For SQL Dialects That Don't Support FILTER #

If you're using a database like MySQL that doesn't support the FILTER syntax, rewrite that part with a conditional sum instead:

SELECT 
    ref_x,
    qty,
    code,
    CASE 
        WHEN code IN (25, 26) THEN 
            SUM(CASE WHEN qty > 0 THEN qty ELSE 0 END) OVER (PARTITION BY ref_x, code)
        WHEN code = 27 THEN 
            SUM(CASE WHEN code = 26 THEN qty ELSE 0 END) OVER (PARTITION BY ref_x)
        ELSE 0
    END AS qtyRemaining
FROM your_table_name;

Testing with Your Sample Data #

When you run either query against your sample data, you'll get exactly the qtyRemaining values you're targeting:

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