如何通过SQL查询各年度最大周数对应的销售数据

需要从销售数据表中查询各年度里周数最大的那一周的销售数据（即年度最后一周的销售记录）。目前只会按年度分组求和，但不知道如何关联各年度的最大周数。

表结构 #

id        sales_person       sales     week_number        year
1           john.d           2000          1              2020
2           john.d           4500          2              2020
...
140         john.d           56000         36             2022

期望结果 #

sales_person           week_number       year       sales
john.d                     52            2020       40000
john.d                     52            2021       50000
john.d                     36            2022       56000

当前问题 #

现有SQL语句冗长，且只能返回首尾两个年度的数据，需要更简洁的写法：

select
    id,
    sales_person,
    sales,
    week_number,
    year
from
    table
where week_number = (
    select max(week_number)
    from table
    where year = (
        select max(year)
        from table
    )
)
and year = (
    select max(year)
    from table
)
and sales_person = 'john.d'
union
select
    id,
    sales_person,
    sales,
    week_number,
    year
from
    table
where week_number = (
    select max(week_number)
    from table
    where year = (
        select min(year)
        from table
    )
)
and year = (
    select min(year)
    from table
)
and sales_person = 'john.d'

方法1：使用窗口函数（推荐，兼容性好） #

利用ROW_NUMBER()窗口函数，按年度分组，给每个年度内的记录按周数倒序排名，取排名为1的记录：

SELECT 
    sales_person,
    week_number,
    year,
    sales
FROM (
    SELECT 
        sales_person,
        week_number,
        year,
        sales,
        ROW_NUMBER() OVER (PARTITION BY year ORDER BY week_number DESC) AS rn
    FROM table
    WHERE sales_person = 'john.d' -- 如果需要查询所有销售人员，可删除此条件
) t
WHERE rn = 1;

方法2：关联子查询 #

先查询每个年度的最大周数，再关联原表获取对应数据：

SELECT 
    t.sales_person,
    t.week_number,
    t.year,
    t.sales
FROM table t
JOIN (
    SELECT year, MAX(week_number) AS max_week
    FROM table
    WHERE sales_person = 'john.d'
    GROUP BY year
) m ON t.year = m.year AND t.week_number = m.max_week
WHERE t.sales_person = 'john.d';

方法3：使用CTE（公共表达式） #

逻辑与方法2一致，用CTE简化语句结构：

WITH year_max_week AS (
    SELECT year, MAX(week_number) AS max_week
    FROM table
    WHERE sales_person = 'john.d'
    GROUP BY year
)
SELECT 
    t.sales_person,
    t.week_number,
    t.year,
    t.sales
FROM table t
JOIN year_max_week m 
    ON t.year = m.year 
    AND t.week_number = m.max_week
WHERE t.sales_person = 'john.d';

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