如何在SQL中为每个ID分组计算并展示最大Outstanding值

Got it, here's a straightforward solution using SQL window functions to get exactly the result you need:

Original Table (Table1) #

Requirement #

Query all fields from Table1, and add a MAX_OUTSTANDING column that shows the maximum value of outstanding for each corresponding ID.

Solution Query #

SELECT 
    outstanding,
    month,
    year,
    ID,
    MAX(outstanding) OVER (PARTITION BY ID) AS MAX_OUTSTANDING
FROM Table1;

How This Works #

The MAX(outstanding) OVER (PARTITION BY ID) part is a window function:

• MAX(outstanding) calculates the highest value in the outstanding column
• PARTITION BY ID splits the data into groups based on each unique ID, so the max is calculated per ID
• Unlike a regular GROUP BY which would collapse rows into a single row per group, this window function preserves all original rows while attaching the correct max value to every row in the group.

Expected Result #

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