mysql group by 实现组内排序

方案一

-- 示例
select settlement.id, settlement.contract_group_id, settlement.create_time
from set_settlement_order settlement
where 1=1
and id in (
    select SUBSTRING_INDEX(group_concat( id order by create_time desc),',',1) 
    from set_settlement_order sso
    where 1=1 
    and sso.deleted = 1
    and sso.approval_type = 0
    and sso.approval_state = 2
    group by sso.contract_group_id
)

方案二

-- 示例
select settlement.id, settlement.contract_group_id, settlement.create_time

from set_settlement_order settlement
inner join (
    select SUBSTRING_INDEX(group_concat( id order by create_time desc),',',1) as id
    
    from set_settlement_order sso
    where 1=1 
    and sso.deleted = 1
    and sso.approval_type = 0
    and sso.approval_state = 2
    group by sso.contract_group_id
    
) as t on t.id = settlement.id

where 1=1
and settlement.contract_group_id = '20181018181323552'

方案三

-- 示例
SELECT settlement.id, settlement.contract_group_id, settlement.create_time

FROM set_settlement_order settlement
WHERE 1=1
<if test="contractGroupIds != null and contractGroupIds.size() > 0">
    and settlement.contract_group_id in
    <foreach collection="contractGroupIds" item="item" separator="," open="(" close=")">
        #{item}
    </foreach>
</if>
AND settlement.id = (
    SELECT sso.id
    FROM set_settlement_order sso
    WHERE sso.contract_group_id = settlement.contract_group_id
    AND sso.deleted = 1
    AND sso.approval_type = 0
    AND sso.approval_state = 2
    ORDER BY sso.create_time DESC
    LIMIT 1
);

原文链接：https://blog.csdn.net/qq_39522120/article/details/108617552