带'%_total'的多条件求和：SQL独立总额校验代码实现求助

It looks like you're trying to validate that the _Total rows in each (Column1, Column2) group match the sum of the individual non-total rows in that group. The issue with your initial attempt is likely that you were trying to compute the group sum within a single SELECT without first isolating the group-level totals. Here's a straightforward approach to get this working:

Step 1: Set Up Sample Data (for Testing) #

First, let's recreate your temp table with the sample data you provided:

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp;
CREATE TABLE #Temp (
    Column1 VARCHAR(10),
    Column2 VARCHAR(10),
    Column3 VARCHAR(20),
    value INT,
    [independent total check] INT
);

INSERT INTO #Temp (Column1, Column2, Column3, value)
VALUES
('A', 'B', 'c1', 10),
('A', 'B', 'c2', 11),
('A', 'B', 'C_total', 21),
('x', 'y', 'z1', 10),
('x', 'y', 'z2', 20),
('x', 'y', 'z_total', 30);

Step 2: Calculate Group Totals & Populate the Check Column #

We'll use a Common Table Expression (CTE) to first compute the sum of non-total rows for each (Column1, Column2) group, then join that back to the original table to fill the independent total check column for the _Total rows:

Option 1: Update the Temp Table (Permanent Change) #

WITH GroupTotals AS (
    SELECT 
        Column1,
        Column2,
        SUM(value) AS group_sum
    FROM #Temp
    WHERE Column3 NOT LIKE '%_Total%' -- Exclude total rows from the sum calculation
    GROUP BY Column1, Column2
)
UPDATE t
SET [independent total check] = gt.group_sum
FROM #Temp t
JOIN GroupTotals gt 
    ON t.Column1 = gt.Column1 
    AND t.Column2 = gt.Column2
WHERE t.Column3 LIKE '%_Total%'; -- Only update the total rows

-- Verify the result
SELECT * FROM #Temp;

Option 2: Select Results (No Permanent Update) #

If you don't want to modify the temp table and just want to return the validated data:

WITH GroupTotals AS (
    SELECT 
        Column1,
        Column2,
        SUM(value) AS group_sum
    FROM #Temp
    WHERE Column3 NOT LIKE '%_Total%'
    GROUP BY Column1, Column2
)
SELECT 
    t.Column1,
    t.Column2,
    t.Column3,
    t.value,
    CASE 
        WHEN t.Column3 LIKE '%_Total%' THEN gt.group_sum
        ELSE NULL -- Keep null for non-total rows as per your sample
    END AS [independent total check]
FROM #Temp t
LEFT JOIN GroupTotals gt 
    ON t.Column1 = gt.Column1 
    AND t.Column2 = gt.Column2;

How This Works #

• The GroupTotals CTE calculates the sum of all individual rows (non-_Total) for each unique (Column1, Column2) pair.
• We then join this CTE back to the original table to link each _Total row to its group's computed sum, which populates the validation column.

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