left join 一对多只取最新一条

要实现left join一对多只取最新一条的效果，可以使用子查询或窗口函数来实现。

使用子查询的方法如下：
SELECT b.student_name, b.student_id, b.date, b.bed, d.punch_time, d.punch_date
FROM student_attendance AS b
LEFT JOIN student_attendance_arrange AS c ON c.id = b.arrange_id
LEFT JOIN (
SELECT student_id, MAX(punch_time) AS max_punch_time
FROM student_punch
GROUP BY student_id
) AS d ON d.student_id=b.student_id AND d.max_punch_time = d.punch_time
WHERE c.type = 4 AND b.date = 20190410 AND b.organization_id = 22 AND b.dorm_room_id=13
GROUP BY b.student_id

使用窗口函数的方法如下：
SELECT student_name, student_id, date, bed, punch_time, punch_date
FROM (
SELECT b.student_name, b.student_id, b.date, b.bed, d.punch_time, d.punch_date,
ROW_NUMBER() OVER (PARTITION BY b.student_id ORDER BY d.punch_time DESC) AS rn
FROM student_attendance AS b
LEFT JOIN student_attendance_arrange AS c ON c.id = b.arrange_id
LEFT JOIN student_punch AS d ON d.student_id=b.student_id
WHERE c.type = 4 AND b.date = 20190410 AND b.organization_id = 22 AND b.dorm_room_id=13
) AS subquery
WHERE rn = 1