如何动态返回各Campaign/Activity对应Expense_Amount最大值的行

你之前的查询逻辑有误：子查询取的是整个费用表的全局最高金额，而不是每个Campaign_Name+Activity_Name组合下的最高费用。这就导致只会返回所有费用等于全局最大值的记录，没法实现每个组合只留自身最高费用的需求。

用窗口函数是最适合这种动态分组取最值的场景，新增Campaign或Activity时，SQL会自动按新的组合分组筛选，不用手动修改逻辑。

方案1：每个组合仅保留1条最高费用记录（用ROW_NUMBER） #

如果同一个组合下有多个相同最高费用的记录，只留其中一条，用ROW_NUMBER()：

WITH RankedExpenses AS (
    SELECT 
        c.Campaign_Name, 
        a.Activity_Name, 
        e.Expense_Amount,
        -- 按活动组合分组，每组内按费用从高到低排号
        ROW_NUMBER() OVER (PARTITION BY c.Campaign_Name, a.Activity_Name ORDER BY e.Expense_Amount DESC) AS row_rank
    FROM Expenses e
    LEFT JOIN Activity a ON e.Activity_Id = a.Activity_Id
    LEFT JOIN Campaign c ON a.Campaign_Id = c.Campaign_Id
    LEFT JOIN Program p ON c.Program_Id = p.Program_Id
)
SELECT Campaign_Name, Activity_Name, Expense_Amount
FROM RankedExpenses
WHERE row_rank = 1;

方案2：保留组合内所有相同最高费用的记录（用RANK） #

如果想把同一个组合下所有费用等于该组最高值的记录都保留，就用RANK()替换上面的ROW_NUMBER()：

WITH RankedExpenses AS (
    SELECT 
        c.Campaign_Name, 
        a.Activity_Name, 
        e.Expense_Amount,
        RANK() OVER (PARTITION BY c.Campaign_Name, a.Activity_Name ORDER BY e.Expense_Amount DESC) AS row_rank
    FROM Expenses e
    LEFT JOIN Activity a ON e.Activity_Id = a.Activity_Id
    LEFT JOIN Campaign c ON a.Campaign_Id = c.Campaign_Id
    LEFT JOIN Program p ON c.Program_Id = p.Program_Id
)
SELECT Campaign_Name, Activity_Name, Expense_Amount
FROM RankedExpenses
WHERE row_rank = 1;

窗口函数里的PARTITION BY c.Campaign_Name, a.Activity_Name就是按活动组合分组，ORDER BY e.Expense_Amount DESC是让每组内费用最高的排在最前面，最后筛选排名第一的记录，就实现了每个组合只留最高费用行的需求，新增组合也能自动适配。

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