MySQL分组排序问题：无法生成目标查询结果求助

我现在需要编写一条MySQL查询语句来生成指定格式的输出，但试了好几条查询都没成功。

实际数据情况 #

我的数据是关联了vendor_charge、vendor_charge_details和charges三张表的结果，其中存在同一charge_id+per+currency组合下有多条不同effective_date的记录，且这些记录都满足vendor_id='12'和effective_date <= '2018-05-22'的条件。

期望输出 #

我需要对每个charge_id+per+currency的组合，只保留**effective_date最新（最大）**的那条完整记录，也就是每个组合仅显示一条最新的条目。

已尝试的查询语句 #

我试过下面两条查询，但都没得到想要的结果：

第一条：

SELECT VCD.id,VCD.effective_date, `VCD`.`charge_id`, `C`.`head`, `VCD`.`per`, `VCD`.`currency`, `VCD`.`amount`, `VCD`.`remarks` 
FROM `vendor_charge` `VC` 
INNER JOIN `vendor_charge_details` `VCD` ON `VC`.`id` = `VCD`.`vc_id` 
LEFT JOIN `charges` `C` ON `C`.`id` = `VCD`.`charge_id` 
WHERE `VC`.`vendor_id` = '12' AND `VCD`.`effective_date` <= '2018-05-22' 
GROUP BY `VCD`.`charge_id`, `VCD`.`per`, `VCD`.`currency` 
ORDER BY `C`.`head` DESC 

第二条：

SELECT VCD.id,VCD.effective_date, `VCD`.`charge_id`, `C`.`head`, `VCD`.`per`, `VCD`.`currency`, `VCD`.`amount`, `VCD`.`remarks` 
FROM `vendor_charge` `VC` 
INNER JOIN `vendor_charge_details` `VCD` ON `VC`.`id` = `VCD`.`vc_id` 
LEFT JOIN `charges` `C` ON `C`.`id` = `VCD`.`charge_id` 
WHERE `VC`.`vendor_id` = '12' AND `VCD`.`effective_date` <= '2018-05-22' 
GROUP BY `VCD`.`charge_id`, `VCD`.`per`, `VCD`.`currency` 
ORDER BY `VCD`.`effective_date` DESC 

问题原因及解决方案 #

之前的查询用GROUP BY时，没有指定要获取每个分组里最新的那条记录——MySQL在使用GROUP BY但不对非分组字段用聚合函数时，会返回分组内的任意一条记录，而不是我们需要的最新日期的那条。

这里提供两种可行的解决方案：

方案1：子查询关联获取最新记录（兼容所有MySQL版本）

先找出每个分组对应的最大effective_date，再关联原表获取完整记录：

SELECT VCD.id, VCD.effective_date, VCD.charge_id, C.head, VCD.per, VCD.currency, VCD.amount, VCD.remarks
FROM vendor_charge VC
INNER JOIN vendor_charge_details VCD ON VC.id = VCD.vc_id
LEFT JOIN charges C ON C.id = VCD.charge_id
INNER JOIN (
    -- 先获取每个分组的最大effective_date
    SELECT charge_id, per, currency, MAX(effective_date) AS max_date
    FROM vendor_charge_details
    WHERE vc_id IN (SELECT id FROM vendor_charge WHERE vendor_id = '12')
      AND effective_date <= '2018-05-22'
    GROUP BY charge_id, per, currency
) AS latest ON VCD.charge_id = latest.charge_id 
           AND VCD.per = latest.per 
           AND VCD.currency = latest.currency 
           AND VCD.effective_date = latest.max_date
WHERE VC.vendor_id = '12'
ORDER BY C.head DESC;

方案2：窗口函数（MySQL 8.0及以上版本适用）

用ROW_NUMBER()窗口函数给每个分组的记录按日期排序，取排序第一的最新记录：

SELECT id, effective_date, charge_id, head, per, currency, amount, remarks
FROM (
    SELECT VCD.id, VCD.effective_date, VCD.charge_id, C.head, VCD.per, VCD.currency, VCD.amount, VCD.remarks,
           -- 按分组排序，最新的日期排第一
           ROW_NUMBER() OVER (PARTITION BY VCD.charge_id, VCD.per, VCD.currency ORDER BY VCD.effective_date DESC) AS rn
    FROM vendor_charge VC
    INNER JOIN vendor_charge_details VCD ON VC.id = VCD.vc_id
    LEFT JOIN charges C ON C.id = VCD.charge_id
    WHERE VC.vendor_id = '12' AND VCD.effective_date <= '2018-05-22'
) AS temp
WHERE rn = 1 -- 只取每个分组的第一条（最新的）
ORDER BY head DESC;

这两种方式都能准确获取到每个组合下最新的那条记录，符合期望输出。

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