如何按年查询各用户对应最晚月份的登录数据？

问题描述 #

现有如下临时表：

CREATE TABLE #temptable 
(
    [Year] int, 
    [Month] int, 
    [User] varchar(255), 
    [Logins] int 
)

INSERT INTO #temptable ([Year], [Month], [User], [Logins])
VALUES (2021, 12, 'Jon', 4), 
       (2021, 12, 'Fred', 5), 
       (2021, 7, 'Jon', 1), 
       (2021, 7, 'Fred', 3), 
       (2022, 12, 'Jon', 9), 
       (2022, 12, 'Fred', 7), 
       (2022, 7, 'Jon', 5), 
       (2022, 7, 'Fred', 1) 

需要编写SQL查询，返回每个用户每年对应最晚月份的登录数据（登录数为累计值，仅需保留每年最晚月份的数据），期望输出如下：

• 2021, Fred, 12, 5
• 2022, Fred, 12, 7
• 2021, Jon, 12, 4
• 2022, Jon, 12, 9

解法一：使用窗口函数ROW_NUMBER() #

利用窗口函数按年份、用户分组，对每组内的月份降序排名，取排名第一的记录即可：

SELECT [Year], [User], [Month], [Logins]
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY [Year], [User] ORDER BY [Month] DESC) AS rn
    FROM #temptable
) t
WHERE rn = 1
ORDER BY [User], [Year]

解法二：子查询关联获取最大月份 #

先按年份和用户分组，找出每组的最大月份，再关联原表匹配对应数据：

SELECT t.[Year], t.[User], t.[Month], t.[Logins]
FROM #temptable t
INNER JOIN (
    SELECT [Year], [User], MAX([Month]) AS MaxMonth
    FROM #temptable
    GROUP BY [Year], [User]
) m ON t.[Year] = m.[Year] AND t.[User] = m.[User] AND t.[Month] = m.MaxMonth
ORDER BY [User], [Year]

补充说明 #

两种方法均可得到目标结果：

• 窗口函数写法简洁直观，支持SQL Server、MySQL 8.0+、PostgreSQL等绝大多数现代SQL数据库；
• 子查询关联方式兼容性更强，可用于不支持窗口函数的旧版本数据库。

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