mysql group by max date_mysql – 使用MAX DATE和GROUP BY获取行

这称为Group-wise Maximum,在此标记为

greatest-n-per-group.最传统的方法是找到所需的值并进行连接以获得每组的相应行,如下所示：

SELECT

ps.id,

ps.due_date,

ps.loan_application_id

FROM

(

SELECT

MAX(due_date) as due_date,

loan_application_id

FROM payment_schedule

WHERE payment_type_id != '3'

GROUP BY loan_application_id

) ps2

LEFT JOIN payment_schedule ps USING (loan_application_id)

WHERE ps.due_date = ps2.due_date

AND ps.payment_type_id != '3'

GROUP BY ps.loan_application_id

还值得一提的是,如果您的loan_application_id和due_date列上有索引,则此查询的运行速度将快上百倍.