如何通过SQL JOIN操作合并两个派生表查询，获取薪资最高的组别名称及对应薪资

嘿，我来帮你搞定这个问题！你想要的是把分组求和和取最大值的逻辑合并，直接得到薪资最高的组名和对应的金额对吧？之前用JOIN没成功可能是关联方式或者分组的细节没处理好，下面给你几种实用的方案：

方案一：子查询JOIN关联 #

这个方法是把你的两个查询作为子表，通过薪资总和字段关联起来，注意要保证分组查询里的GROUP BY包含所有非聚合字段（避免SQL模式报错）：

SELECT t1.con_stagename, t1.sum_salary
FROM (
    -- 先获取每个组的薪资总和
    SELECT con_stagename, SUM(p_daily_salary) AS sum_salary 
    FROM CONTENDER
    JOIN PARTICIPANT ON PARTICIPANT.p_contender = CONTENDER.con_id  -- 用显式JOIN代替逗号，可读性更强
    GROUP BY con_id, con_stagename  -- 加上con_stagename，符合SQL标准
) t1
JOIN (
    -- 再获取薪资总和的最大值
    SELECT MAX(sum_salary) AS max_salary 
    FROM (
        SELECT SUM(p_daily_salary) AS sum_salary 
        FROM CONTENDER
        JOIN PARTICIPANT ON PARTICIPANT.p_contender = CONTENDER.con_id
        GROUP BY con_id
    ) t2
) t3 ON t1.sum_salary = t3.max_salary;

方案二：窗口函数（推荐，更简洁） #

如果你的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server等），用RANK()或者ROW_NUMBER()会更优雅，还能处理多个组薪资并列第一的情况：

WITH group_salaries AS (
    SELECT 
        con_stagename, 
        SUM(p_daily_salary) AS sum_salary,
        -- 按薪资总和降序排名，并列第一的组会有相同的rank值
        RANK() OVER (ORDER BY SUM(p_daily_salary) DESC) AS salary_rank
    FROM CONTENDER
    JOIN PARTICIPANT ON PARTICIPANT.p_contender = CONTENDER.con_id
    GROUP BY con_id, con_stagename
)
-- 取排名第一的记录
SELECT con_stagename, sum_salary
FROM group_salaries
WHERE salary_rank = 1;

• 如果只需要返回任意一个薪资最高的组，把RANK()换成ROW_NUMBER()即可；
• 如果需要所有并列第一的组，RANK()和DENSE_RANK()都可以，区别是DENSE_RANK()不会跳过排名序号。

方案三：HAVING子句直接匹配最大值 #

这种方法不需要额外的JOIN，直接在分组后的HAVING条件里判断当前组的薪资总和等于最大值：

SELECT con_stagename, SUM(p_daily_salary) AS sum_salary
FROM CONTENDER
JOIN PARTICIPANT ON PARTICIPANT.p_contender = CONTENDER.con_id
GROUP BY con_id, con_stagename
HAVING SUM(p_daily_salary) = (
    SELECT MAX(sum_salary)
    FROM (
        SELECT SUM(p_daily_salary) AS sum_salary
        FROM CONTENDER
        JOIN PARTICIPANT ON PARTICIPANT.p_contender = CONTENDER.con_id
        GROUP BY con_id
    ) t2
);

为什么你之前的JOIN可能失败？ #

大概率是这两个原因：

1. 原查询的GROUP BY只写了con_id，但SELECT里包含con_stagename，在开启ONLY_FULL_GROUP_BY模式的数据库（比如默认设置的MySQL 8+）里会报错，导致子查询无法正常执行；
2. 关联的时候没有正确匹配sum_salary和max_salary字段，或者子表别名使用不当。

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