Oracle SQL实现获取每个草坪地块最新割草原因的需求

我有一份Oracle SQL报表，用于返回本财年内草坪地块的所有割草记录，当前返回的数据示例如下：

Central Asset ID         Grass Cut?        Grass Cut Reason          Cut Date

1234                     Yes              Yes - Grass Cut            14/04/2023

1234                     No               No - Ground waterlogged    28/4/2023

我需要获取每个地块最新割草日期对应的Central Asset ID和Grass Cut Reason，比如上述示例应返回：

Central Asset ID  1234
Grass Cut Reason "No - Ground waterlogged"

以下是我目前的SQL代码：

select
feature.central_asset_id,
case
when observe_parm_opt.obs_parm_opt_name like 'Yes%' then 'Cut' 
when observe_parm_opt.obs_parm_opt_name like 'No%' then 'Not Cut'   
when observe_parm_opt.obs_parm_opt_name is NULL  then '' 
else ''     
end as grasscut,
Max(observe_parm_opt.obs_parm_opt_name) as Grass_Cut_Reason,
max(inspection_feature.feature_insp_date) as Insp_Date

from
feature
inner join inspection_feature on inspection_feature.site_code = feature.site_code and inspection_feature.plot_number = feature.plot_number
inner join insp_condition on inspection_feature.insp_batch_no = insp_condition.insp_batch_no and
inspection_feature.site_code = insp_condition.site_code and inspection_feature.plot_number = insp_condition.plot_number

inner join observe_type on insp_condition.observe_type_key = observe_type.observe_type_key
inner join observe_parameter on observe_type.obs_parm_code = observe_parameter.obs_parm_code
inner join observe_parm_opt on observe_parameter.obs_parm_code = observe_parm_opt.obs_parm_code
inner join central_site on inspection_feature.site_code = central_site.site_code
inner join feature_type on feature.feature_type_code = feature_type.feature_type_code

where
insp_condition.grade_code = observe_parm_opt.obs_parm_opt_code and observe_type.observe_type_code = 'G003' and
inspection_feature.feature_insp_date >= TO_DATE('30/03/2022','DD/MM/YYYY') and
feature.feature_type_code in ('G005')

group by
feature.central_asset_id,
observe_parm_opt.obs_parm_opt_name

现有代码的问题在于分组时包含了observe_parm_opt.obs_parm_opt_name，会导致同一资产ID因不同割草原因被拆分多行，且MAX(obs_parm_opt_name)无法匹配到最新日期的对应记录。可以通过窗口函数ROW_NUMBER()精准筛选每个资产的最新记录：

WITH ranked_records AS (
    SELECT
        f.central_asset_id,
        opo.obs_parm_opt_name AS Grass_Cut_Reason,
        ife.feature_insp_date,
        -- 按资产ID分组，日期倒序排序，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY f.central_asset_id ORDER BY ife.feature_insp_date DESC) AS rn
    FROM feature f
    INNER JOIN inspection_feature ife 
        ON ife.site_code = f.site_code AND ife.plot_number = f.plot_number
    INNER JOIN insp_condition ic 
        ON ic.insp_batch_no = ife.insp_batch_no 
        AND ic.site_code = ife.site_code 
        AND ic.plot_number = ife.plot_number
    INNER JOIN observe_type ot 
        ON ic.observe_type_key = ot.observe_type_key
    INNER JOIN observe_parameter op 
        ON ot.obs_parm_code = op.obs_parm_code
    INNER JOIN observe_parm_opt opo 
        ON op.obs_parm_code = opo.obs_parm_code
    -- 若central_site和feature_type未参与过滤或返回字段，可移除关联以提升效率
    -- INNER JOIN central_site cs ON ife.site_code = cs.site_code
    -- INNER JOIN feature_type ft ON f.feature_type_code = ft.feature_type_code
    WHERE
        ic.grade_code = opo.obs_parm_opt_code 
        AND ot.observe_type_code = 'G003' 
        AND ife.feature_insp_date >= TO_DATE('30/03/2022','DD/MM/YYYY') 
        AND f.feature_type_code = 'G005'
)
SELECT
    central_asset_id,
    Grass_Cut_Reason
FROM ranked_records
WHERE rn = 1; -- 仅保留每个资产的最新记录

关键说明 #

1. 用CTE（公共表表达式）ranked_records为每个资产的记录按日期倒序排名，最新记录的rn值为1
2. 移除了原代码中需求外的grasscut字段和冗余分组，聚焦目标返回字段
3. 可按需移除未参与业务逻辑的表关联，优化查询性能

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