MySQL如何获取GROUP BY聚合后对应分组的原始行数据？

现有一张包含total_sale、total_profit、total_rent、total_rent_expense、total_expense、the_date字段的表格，当前使用以下SQL按年份分组，对total_sale和total_rent求和得到highest_income，再按highest_income降序取第一条记录：

SELECT
    SUM(temp_t.total_sale + temp_t.total_rent) highest_income,
    temp_t.total_sale,
    temp_t.total_profit,
    temp_t.total_rent,
    temp_t.total_rent_expense,
    temp_t.total_expense,
    YEAR ( temp_t.the_date ) AS the_date
FROM
    ...
GROUP BY
    temp_t.grouping_column 
ORDER BY
    highest_income DESC 
LIMIT 1

但执行后无法获取聚合后对应年份的原始行中total_rent等列的具体值，期望得到类似归属2022年的完整行数据：
381266.9600 122961.9600 51163.9800 258305.00 16959.17 60780.00 2022
请问该如何实现？

方法1：使用窗口函数（推荐） #

利用ROW_NUMBER()窗口函数先计算每个年份的highest_income，再按收入排序标记每条记录，最后取目标年份的对应行。该方法兼容MySQL 8.0+、PostgreSQL、SQL Server等大多数现代数据库：

WITH year_income AS (
    SELECT
        SUM(total_sale + total_rent) OVER (PARTITION BY YEAR(the_date)) AS highest_income,
        total_sale,
        total_profit,
        total_rent,
        total_rent_expense,
        total_expense,
        YEAR(the_date) AS the_date,
        ROW_NUMBER() OVER (
            PARTITION BY YEAR(the_date)
            ORDER BY SUM(total_sale + total_rent) OVER (PARTITION BY YEAR(the_date)) DESC
        ) AS rn
    FROM
        ... -- 替换为你的原表或子查询temp_t
)
SELECT
    highest_income,
    total_sale,
    total_profit,
    total_rent,
    total_rent_expense,
    total_expense,
    the_date
FROM year_income
WHERE rn = 1
ORDER BY highest_income DESC
LIMIT 1;

若需获取最高收入年份的所有原始行，可去掉末尾的LIMIT 1。

方法2：子查询关联（适配旧版数据库） #

先计算各年份的总收入并找到最高收入对应的年份，再关联原表获取该年份的具体行数据，适合不支持窗口函数的旧版数据库（如MySQL 5.x）：

-- 第一步：计算各年份总收入，锁定最高收入的年份
WITH year_total AS (
    SELECT
        YEAR(the_date) AS year,
        SUM(total_sale + total_rent) AS highest_income
    FROM
        ... -- 替换为你的原表或子查询temp_t
    GROUP BY YEAR(the_date)
    ORDER BY highest_income DESC
    LIMIT 1
)
-- 第二步：关联原表提取该年份的完整行数据
SELECT
    yt.highest_income,
    t.total_sale,
    t.total_profit,
    t.total_rent,
    t.total_rent_expense,
    t.total_expense,
    yt.year AS the_date
FROM year_total yt
JOIN ... t -- 替换为你的原表或子查询temp_t
ON YEAR(t.the_date) = yt.year;

注意事项 #

• 若同一年份存在多条原始记录，方法1会返回该年份中对应最高收入聚合值的第一条记录（可调整ORDER BY子句指定取哪条）；方法2会返回该年份的所有原始记录。
• 需将SQL中的...替换为你的实际表名或子查询逻辑。

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