MySQL5.6不使用@变量生成row_number获取各账号2021年前最新余额记录

• 行号计算逻辑错误：原SQL子查询使用a1.id < a2.id作为计数条件，统计的是全表id小于当前行的记录总数，没有按账号分组，也没有按日期排序，无法得到同账号下的记录行号。
• 过滤条件错误：原SQL使用val_dt <= '2021-01-01'作为筛选条件，会把覆盖2021年新年、失效时间在新年之后的有效记录过滤掉，不符合业务需求。正确的筛选逻辑应为记录生效时间早等于2021年新年、失效时间晚等于2021年新年，即2021年新年当天处于记录有效期内。

以下方案兼容MySQL 5.6及以上版本，不需要使用用户变量，不依赖固定的账号数、记录数，可直接适配不同的查询时间点：

SELECT 
  a2.id, 
  a2.nom, 
  (
    SELECT COUNT(*) 
    FROM account a1 
    WHERE a1.id = a2.id 
      AND a1.bal_dt <= '2021-01-01' 
      AND a1.val_dt >= '2021-01-01'
      AND a1.bal_dt > a2.bal_dt
  ) + 1 AS rn,
  a2.bal_dt AS val_dt,
  a2.bal
FROM account a2
WHERE a2.bal_dt <= '2021-01-01' 
  AND a2.val_dt >= '2021-01-01'
HAVING rn = 1
ORDER BY a2.id;

逻辑说明 #

通过关联子查询模拟分组排序的行号逻辑：同账号下统计比当前记录生效时间更晚的有效记录数，计数加1即为当前记录的行号。当计数为0时行号为1，代表该账号下最新的有效记录。

运行后输出结果和期望样式完全一致：

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