mysql - MySQL Query with Left Join、Order By 和 Limit 以及其他连接

Question

试图从多个表中获取数据和子数据。这里的重要部分是加入 student_passports 数据/表。其他一切工作正常（尽管如果您有关于如何改进查询的提示，那将始终不胜感激）。我实际上是在尝试返回一行，即学生现有的“最新”护照。我尝试了多种方法，但没有获胜。第一种似乎是最合乎逻辑的方法。我把它们都放在下面了...

这似乎是正确的方法，但 Left Join 返回一堆 NULL 值（除了 1 返回的行实际上具有 passport_number 和 passport_expiry_date 的值）：

SELECT 
    students.id AS sid,
    bursary_administrator_student.bursary_administrator_reference, students.student_name, students.student_surname,
    students.id_number, stud_pass.passport_number, stud_pass.passport_expiry_date,
    
    (SELECT STATUS FROM student_bursary_statuses 
            INNER JOIN student_bursaries ON student_bursaries.id=student_bursary_statuses.student_bursary_id
            INNER JOIN students ON students.id=student_bursaries.student_id
    WHERE student_id=sid AND (status_start<=NOW() AND (status_end>=NOW() OR status_end IS NULL)) LIMIT 1) AS STATUS,

    (SELECT contact_detail FROM student_contact_details 
    INNER JOIN students ON student_contact_details.`student_id`=students.id
    INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
    WHERE `primary`=1 AND contact_detail_type='Mobile ZA' AND student_id=sid LIMIT 1) AS primary_mobile,
    
    (SELECT contact_detail FROM student_contact_details
    INNER JOIN students ON student_contact_details.`student_id`=students.id
    INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
    WHERE `primary`=1 AND contact_detail_type='Email' AND student_id=sid LIMIT 1) AS primary_email
    
FROM students
LEFT JOIN 
    (SELECT student_id, passport_number, passport_expiry_date FROM student_passports 
    ORDER BY passport_expiry_date DESC LIMIT 1) stud_pass ON students.id=stud_pass.student_id
INNER JOIN bursary_administrator_student ON students.id=bursary_administrator_student.student_id
WHERE bursary_administrator_student.bursary_administrator_id=1
ORDER BY students.id

我尝试了这个左连接，但需要 30 秒来执行（不是一个大的数据库，每个表中只有几百行）并最终返回 132,148 行，而它应该在 360 左右 - 所以很明显返回了一个倍数：

SELECT 
    students.id AS sid,
    bursary_administrator_student.bursary_administrator_reference, students.student_name, students.student_surname,
    students.id_number, student_passports.passport_number, student_passports.passport_expiry_date,
    
    (SELECT STATUS FROM student_bursary_statuses 
    INNER JOIN student_bursaries ON student_bursaries.id=student_bursary_statuses.student_bursary_id
    INNER JOIN students ON students.id=student_bursaries.student_id
    WHERE student_id=sid AND (status_start<=NOW() AND (status_end>=NOW() OR status_end IS NULL)) LIMIT 1) AS STATUS,

    (SELECT contact_detail FROM student_contact_details 
    INNER JOIN students ON student_contact_details.`student_id`=students.id
    INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
    WHERE `primary`=1 AND contact_detail_type='Mobile ZA' AND student_id=sid LIMIT 1) AS primary_mobile,
    
    (SELECT contact_detail FROM student_contact_details
    INNER JOIN students ON student_contact_details.`student_id`=students.id
    INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
    WHERE `primary`=1 AND contact_detail_type='Email' AND student_id=sid LIMIT 1) AS primary_email
    
FROM students
LEFT JOIN student_passports ON students.`id`= (SELECT student_passports.`student_id` FROM student_passports WHERE student_passports.`student_id`=students.id ORDER BY passport_expiry_date DESC LIMIT 1)

INNER JOIN bursary_administrator_student ON students.id=bursary_administrator_student.student_id
WHERE bursary_administrator_student.bursary_administrator_id=1
ORDER BY students.id

“有效”，但是当 passport_expiry_date 对多行具有相同的值时，这很脏并且容易出现数据错误：

SELECT 
    students.id AS sid,
    bursary_administrator_student.bursary_administrator_reference, students.student_name, students.student_surname,
    students.id_number, 
    
    (SELECT student_passports.passport_number FROM student_passports
    INNER JOIN students ON student_passports.`student_id`=students.id
    WHERE student_id=sid ORDER BY passport_expiry_date DESC LIMIT 1) AS passport_number,

    (SELECT student_passports.passport_expiry_date FROM student_passports
    INNER JOIN students ON student_passports.`student_id`=students.id
    WHERE student_id=sid ORDER BY passport_expiry_date DESC LIMIT 1) AS passport_expiry_date,

    (SELECT STATUS FROM student_bursary_statuses 
    INNER JOIN student_bursaries ON student_bursaries.id=student_bursary_statuses.student_bursary_id
    INNER JOIN students ON students.id=student_bursaries.student_id
    WHERE student_id=sid AND (status_start<=NOW() AND (status_end>=NOW() OR status_end IS NULL)) LIMIT 1) AS STATUS,

    (SELECT contact_detail FROM student_contact_details 
    INNER JOIN students ON student_contact_details.`student_id`=students.id
    INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
    WHERE `primary`=1 AND contact_detail_type='Mobile ZA' AND student_id=sid LIMIT 1) AS primary_mobile,
    
    (SELECT contact_detail FROM student_contact_details
    INNER JOIN students ON student_contact_details.`student_id`=students.id
    INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
    WHERE `primary`=1 AND contact_detail_type='Email' AND student_id=sid LIMIT 1) AS primary_email
    
FROM students
INNER JOIN bursary_administrator_student ON students.id=bursary_administrator_student.student_id
WHERE bursary_administrator_student.bursary_administrator_id=1
ORDER BY students.id

// 编辑：这是第三个/上述解决方案的更好版本，我选择匹配的 student_passport 的 ID，然后使用该 ID 选择相应记录的相关两个字段。仍然认为必须有更好的方法来做到这一点：

SELECT 
students.id AS sid,
bursary_administrator_student.bursary_administrator_reference, students.student_name, students.student_surname,
students.id_number, 

(SELECT student_passports.id FROM student_passports
INNER JOIN students ON student_passports.`student_id`=students.id
WHERE student_id=sid ORDER BY passport_expiry_date DESC LIMIT 1) AS student_passports_id,

    (SELECT student_passports.passport_number FROM student_passports
    INNER JOIN students ON student_passports.`student_id`=students.id
    WHERE student_passports.id=student_passports_id ORDER BY passport_expiry_date DESC LIMIT 1) AS passport_number,

    (SELECT student_passports.passport_expiry_date FROM student_passports
    INNER JOIN students ON student_passports.`student_id`=students.id
    WHERE student_passports.id=student_passports_id ORDER BY passport_expiry_date DESC LIMIT 1) AS passport_expiry_date,

(SELECT STATUS FROM student_bursary_statuses 
INNER JOIN student_bursaries ON student_bursaries.id=student_bursary_statuses.student_bursary_id
INNER JOIN students ON students.id=student_bursaries.student_id
WHERE student_id=sid AND (status_start<=NOW() AND (status_end>=NOW() OR status_end IS NULL)) LIMIT 1) AS STATUS,

(SELECT contact_detail FROM student_contact_details 
INNER JOIN students ON student_contact_details.`student_id`=students.id
INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
WHERE `primary`=1 AND contact_detail_type='Mobile ZA' AND student_id=sid LIMIT 1) AS primary_mobile,

(SELECT contact_detail FROM student_contact_details
INNER JOIN students ON student_contact_details.`student_id`=students.id
INNER JOIN contact_detail_types ON student_contact_details.`contact_detail_type_id`=contact_detail_types.id
WHERE `primary`=1 AND contact_detail_type='Email' AND student_id=sid LIMIT 1) AS primary_email

FROM students
INNER JOIN bursary_administrator_student ON students.id=bursary_administrator_student.student_id
WHERE bursary_administrator_student.bursary_administrator_id=1
ORDER BY students.id

标签： mysql