mysql - MySQL中的组和顺序

我必须编写一个查询，其中需要给定的输出。
我尝试了其他查询，但是没有用。

实际数据：



我需要像这样的输出：


查询如：

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

最佳答案

我认为您只需要在子查询中添加一个附加联接，即可为每个effective_date查找最新的charge_id：

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

关于mysql - MySQL中的组和顺序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50637337/