如何编写SQL查询返回不超过传入月份的最近存储月份对应记录

需求说明 #

编写SQL查询，传入月份参数后，返回不超过该参数值的、表中已存储的最近月份对应的全部目标记录，样例场景：

• 当传入月份参数为6、查询用户为brad时，表中brad最大的符合<=6的月份是4，返回brad对应4月的两条记录
• 当传入月份参数为3时，返回brad对应3月的记录

样表结构与测试数据 #

表主键为username、month、value，样例数据如下：

username, month, value, ...(其他字段)
--------------------------
brad,     2,     d34
fred,     2,     x12
bob,      2,     y45
brad,     3,     d56
fred,     3,     x67
brad,     4,     d56
brad,     4,     d34
fred,     6,     x78
brad,     8,     d89

原有固定写法（仅支持查month=4的brad记录）：

SELECT username, value FROM table WHERE username = 'brad' and month = 4

优化后查询方案 #

方案1：子查询匹配最大月份（单用户查询场景，兼容性最好） #

定义两个传入参数：@target_username（要查询的用户名）、@input_month（传入的月份阈值），查询语句如下：

SELECT username, value 
FROM 你的表名
WHERE username = @target_username
  AND month = (
    -- 子查询找出符合条件的最大月份
    SELECT MAX(month) 
    FROM 你的表名
    WHERE username = @target_username
      AND month <= @input_month
  )

方案2：窗口函数写法（支持批量查询多用户的最近月份记录） #

如果需要同时查询所有用户对应不超过阈值的最近月份记录，可以用窗口函数实现：

WITH month_rank AS (
  SELECT 
    username, 
    value,
    -- 按用户分组，月份倒序排名，最大的月份排名为1
    RANK() OVER (PARTITION BY username ORDER BY month DESC) AS rk
  FROM 你的表名
  WHERE month <= @input_month
)
SELECT username, value 
FROM month_rank 
WHERE rk = 1
-- 如需查询单个用户，可追加条件：AND username = @target_username

效果验证 #

以传入参数@target_username='brad'、@input_month=6为例：

• 子查询/窗口函数筛选出brad符合month<=6的最大月份为4
• 最终返回brad对应4月的d56、d34两条记录，符合需求

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