获取每日最高版本号的小时级数据的SQL查询问题

表结构 #

主表（wb_declared_capacity_master） #

明细表（wb_declared_capacity_detail） #

需求 #

每日的容量数据会以版本（revision）形式多次保存，部分日期可能有1个版本，部分可能有多个版本。需要通过单条SQL查询，获取每个日期的最高版本对应的小时级数据，2022年1月的3个日期应返回12行数据。

原查询及问题 #

原SQL查询：

select a1.wdcm_date as wdcm_date, c1.wdcd_block_no as wdcd_block_no, c1.wdcd_capacity as wdcd_capacity, 
      c1.wdcd_approval as wdcd_approval, a1.wdcm_revision_no as wdcm_revision_no
from wb_declared_capacity_master a1, wb_declared_capacity_detail c1
where a1.wdcm_internal_id = c1.wdcd_ref_id  and to_char(a1.wdcm_date,'MM yyyy')='01 2022' 
and wdcm_revision_no = (select max(wdcm_revision_no) from wb_declared_capacity_master where to_char(wdcm_date,'MM yyyy')='01 2022')

该查询仅返回版本号为3的数据，无法得到预期的12行结果：

预期结果 #

修改方案 #

原查询的核心问题是：子查询取的是整个2022年1月的最大版本号（3），而非每个日期自身的最大版本号。以下两种方法可解决该问题：

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

SELECT 
    t.wdcm_date,
    c1.wdcd_block_no,
    c1.wdcd_capacity,
    c1.wdcd_approval,
    t.wdcm_revision_no
FROM (
    SELECT 
        wdcm_internal_id,
        wdcm_date,
        wdcm_revision_no,
        -- 按日期分组，版本号降序排序，标记每个日期的最高版本为行号1
        ROW_NUMBER() OVER (PARTITION BY wdcm_date ORDER BY wdcm_revision_no DESC) AS rn
    FROM wb_declared_capacity_master
    WHERE TO_CHAR(wdcm_date, 'MM yyyy') = '01 2022'
) t
JOIN wb_declared_capacity_detail c1 ON t.wdcm_internal_id = c1.wdcd_ref_id
WHERE t.rn = 1
ORDER BY t.wdcm_date, c1.wdcd_block_no;

方法二：关联子查询按日期取最大版本 #

SELECT 
    a1.wdcm_date,
    c1.wdcd_block_no,
    c1.wdcd_capacity,
    c1.wdcd_approval,
    a1.wdcm_revision_no
FROM wb_declared_capacity_master a1
JOIN wb_declared_capacity_detail c1 ON a1.wdcm_internal_id = c1.wdcd_ref_id
WHERE TO_CHAR(a1.wdcm_date, 'MM yyyy') = '01 2022'
-- 关键：子查询匹配当前行的日期，取该日期的最大版本号
AND a1.wdcm_revision_no = (
    SELECT MAX(wdcm_revision_no) 
    FROM wb_declared_capacity_master 
    WHERE wdcm_date = a1.wdcm_date
)
ORDER BY a1.wdcm_date, c1.wdcd_block_no;

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