按求和最大值关联主ID(ID1)与子ID(ID2)的SQL查询需求

Got it, let's fix this query for you. The goal is to group by ID1, calculate the total Value for each ID2 in the group, then pick the ID2 with the highest total. Here's a working solution that uses window functions to get the desired result:

WITH id2_totals AS (
    -- First, calculate the total Value for each ID1-ID2 pair
    SELECT 
        ID1, 
        ID2, 
        SUM(Value) AS total_value
    FROM your_table_name -- Replace this with your actual table name
    GROUP BY ID1, ID2
),
ranked_totals AS (
    -- Assign a rank to each ID2 within its ID1 group, sorted by total value descending
    SELECT 
        ID1, 
        ID2, 
        ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY total_value DESC) AS rank_num
    FROM id2_totals
)
-- Grab only the top-ranked ID2 for each ID1
SELECT ID1, ID2
FROM ranked_totals
WHERE rank_num = 1;

Breakdown of how this works: #

• First CTE (id2_totals): We group the data by both ID1 and ID2 to compute the sum of Value for each pair. This gives us the total value each ID2 contributes to its parent ID1.
• Second CTE (ranked_totals): Using the ROW_NUMBER() window function, we assign a unique rank to each ID2 within its ID1 group. The rank is ordered by the total value in descending order, so the ID2 with the highest sum gets rank 1.
• Final SELECT: We filter for rows where the rank is 1, which gives us exactly the ID2 with the maximum total value for each ID1.

Handling ties (if needed): #

If there's a case where multiple ID2s have the same maximum total value for an ID1, ROW_NUMBER() will pick one arbitrarily. If you want to return all tied ID2s instead, swap ROW_NUMBER() with RANK():

RANK() OVER (PARTITION BY ID1 ORDER BY total_value DESC) AS rank_num

For older SQL versions (no CTE support): #

If your database doesn't allow Common Table Expressions (CTEs), you can rewrite this using a subquery instead:

SELECT ID1, ID2
FROM (
    SELECT 
        ID1, 
        ID2, 
        ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY SUM(Value) DESC) AS rank_num
    FROM your_table_name
    GROUP BY ID1, ID2
) AS ranked_data
WHERE rank_num = 1;

Content of the question来源于stack exchange，提问作者Analyst Guy