SQL分组求和与最大值查询：同Val1、Val2分组求总和并取最大CValue对应ID

First, let's clarify the requirements with your sample data:

• Group rows where Val1 and Val2 are identical
• For each group, calculate the total sum of CValue
• Retrieve the ID from the row in the group with the highest CValue

Here are two clean approaches to solve this in SQL, depending on your database's support for window functions (most modern databases like PostgreSQL, MySQL 8+, SQL Server support them).

Approach 1: Using Window Functions (Recommended) #

Window functions make this task concise and efficient, as they let us compute aggregations without grouping the entire table first.

WITH grouped_stats AS (
    SELECT
        ID,
        Val1,
        Val2,
        CValue,
        -- Calculate total CValue for the group
        SUM(CValue) OVER (PARTITION BY Val1, Val2) AS total_cvalue,
        -- Find the maximum CValue in the group
        MAX(CValue) OVER (PARTITION BY Val1, Val2) AS max_cvalue_in_group
    FROM your_table_name
)
-- Select distinct groups, filtering to only rows with the max CValue
SELECT DISTINCT
    Val1,
    Val2,
    total_cvalue,
    ID AS max_cvalue_id
FROM grouped_stats
WHERE CValue = max_cvalue_in_group;

Handling Ties (Multiple Rows with Same Max CValue) #

If there are multiple rows in a group with the same highest CValue, the above query will return all their IDs. To pick just one (e.g., the smallest ID), modify the query to use FIRST_VALUE:

WITH grouped_stats AS (
    SELECT
        Val1,
        Val2,
        SUM(CValue) OVER (PARTITION BY Val1, Val2) AS total_cvalue,
        -- Pick the first ID when ordering by CValue descending, then ID ascending
        FIRST_VALUE(ID) OVER (
            PARTITION BY Val1, Val2 
            ORDER BY CValue DESC, ID ASC
        ) AS max_cvalue_id
    FROM your_table_name
)
SELECT DISTINCT
    Val1,
    Val2,
    total_cvalue,
    max_cvalue_id
FROM grouped_stats;

Approach 2: Using Subqueries and Joins #

If your database doesn't support window functions, this traditional approach works:

-- First, calculate total CValue per group
WITH sum_per_group AS (
    SELECT
        Val1,
        Val2,
        SUM(CValue) AS total_cvalue
    FROM your_table_name
    GROUP BY Val1, Val2
),
-- Then find the ID(s) of rows with max CValue per group
max_id_per_group AS (
    SELECT
        t1.Val1,
        t1.Val2,
        t1.ID AS max_cvalue_id
    FROM your_table_name t1
    JOIN (
        SELECT Val1, Val2, MAX(CValue) AS max_cvalue
        FROM your_table_name
        GROUP BY Val1, Val2
    ) t2 
    ON t1.Val1 = t2.Val1 
    AND t1.Val2 = t2.Val2 
    AND t1.CValue = t2.max_cvalue
)
-- Join the two results to get final output
SELECT
    s.Val1,
    s.Val2,
    s.total_cvalue,
    m.max_cvalue_id
FROM sum_per_group s
JOIN max_id_per_group m 
ON s.Val1 = m.Val1 
AND s.Val2 = m.Val2;

Result for Your Sample Data #

Both approaches will return:

Just replace your_table_name with the actual name of your table, and you're good to go!

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