1.使用order by + limit
比如
SELECT *
FROM apply_info ai
LEFT JOIN audit_log al
ON al.log_id = (
SELECT al_in.log_id
FROM audit_log al_in
WHERE al_in.info_id = ai.info_id
ORDER BY al_in.audit_time DESC
LIMIT 1
);
2.使用max(),min()等分析函数+group by
SELECT
c.,
p.
FROM
customer c
INNER JOIN
(
SELECT
customer_id,
MAX(DATE) MaxDate
FROM
purchase
GROUP BY
customer_id ) MaxDates
ON
c.id = MaxDates.customer_id
INNER JOIN
purchase p
ON
MaxDates.customer_id = p.customer_id
AND MaxDates.MaxDate = p.date
3.使用rank()或row_number()
SELECT
c.,
p.
FROM
customer c
INNER JOIN
(
SELECT
RANK() OVER (PARTITION BY customer_id ORDER BY DATE DESC) r,
*
FROM
purchase) p
ON
(
c.id = p.customer_id)
WHERE p.r = 1