mysqlleftjoin多对多获取最新编辑的⼀条
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
anization_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
anization_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.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,
多表left join
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( d_time, '' ) AS vocation_end_time,
IFNULL( ason, '' ) 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'
anization_id = 4954
AND sav.deleted_at IS NULL
AND s.`status` = 1
GROUP BY
s.id
ORDER BY
sav.id DESC

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。