mysql 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 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 GROUP BY b.student_id

这里默认获取的是 d表中的第一条，punch_time不是最新的时间

SELECT
                            b.student_name,
                            b.student_id,
                            b.date,
                            b.bed,
                            max(d.punch_time) as 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 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 GROUP BY b.student_id

这里利用mysql 的max函数，就能获取到student_punch表里最新的一条数据了

但是利用mac函数，会出现max(字段)的值为最大，但是其他列表的值并不是与这个最大值为同一记录里的，这里就需要用到子查询，选择符合条件的记录查询出来，再进行left join

SELECT
	s.id AS student_id,
	s.truename AS student_name,
	s.avatar AS student_avatar,
	s.card AS student_card,
	IFNULL( os.id, 0 ) AS class_id,
	IFNULL( os.title, '' ) AS class_title,
	IFNULL( os.grade_value, 0 ) AS grade_id,
	IFNULL( os.grade_title, '' ) AS grade_title,
	IFNULL( sd.dorm_id, 0 ) AS dorm_id,
	IFNULL( d.title, '' ) AS dorm_title,
	IFNULL( sd.bed, '' ) AS bed,
	IFNULL( dr.floor, 0 ) AS floor,
	IFNULL( sd.dorm_room_id, 0 ) AS dorm_room_id,
	IFNULL( dr.title, '' ) AS dorm_room_title,
	IFNULL( sav.start_time, '' ) AS vocation_start_time,
	IFNULL( sav.end_time, '' ) AS vocation_end_time,
	IFNULL( sav.reason, '' ) AS vocation_reason 
FROM
	students s
	LEFT JOIN organization_classes os ON os.id = s.class_id
	LEFT JOIN student_dorm sd ON sd.student_id = s.id
	LEFT JOIN dorm_room dr ON dr.id = sd.dorm_room_id
	LEFT JOIN dorm d ON d.id = sd.dorm_id
	LEFT JOIN (
	SELECT
		student_id,id,start_time,end_time,reason,deleted_at
	FROM
		student_attendance_vacation 
	WHERE
		organization_id = 4954 
	ORDER BY
		id DESC 
		LIMIT 1 
	) sav ON sav.student_id = s.id 
WHERE
	s.card = '6832975' 
	AND s.organization_id = 4954 
	AND sav.deleted_at IS NULL 
	AND s.`status` = 1 
GROUP BY
	s.id 
ORDER BY
	sav.id DESC