我有這個查詢是為我正在維護的應用程式撰寫的，它是檢查學生的結果，其他學生的結果作業正常，但對于一些它回傳Query error: Subquery returns more than 1 row
$this->db->query("SELECT subjects.name AS subject, subjects.id as subject_id, marks.CA AS ca, marks.exam AS exam, marks.mark_obtained AS total, marks.percentages AS percentages, marks.student_id, marks.subject_id, marks.class_id, marks.section_id, marks.session, marks.term, marks.school_id, (SELECT grades.name FROM grades WHERE marks.mark_obtained >= grades.mark_from AND marks.mark_obtained <= grades.mark_upto AND grades.school_id=$school_id) AS grade, (SELECT grades.comment FROM grades WHERE marks.mark_obtained >= grades.mark_from AND marks.mark_obtained <= grades.mark_upto AND grades.school_id=$school_id) AS remark FROM users INNER JOIN students ON users.id = students.user_id INNER JOIN marks ON students.id = marks.student_id INNER JOIN subjects ON marks.subject_id = subjects.id WHERE marks.school_id = ? AND marks.student_id = ? AND marks.session = ? AND marks.term = ? AND marks.class_id = ? AND marks.section_id = ? ORDER BY marks.subject_id", array($school_id, $student_id, $session_id, $term, $class_id, $section_id))->result_array();
uj5u.com熱心網友回復：
像這樣在查詢中使用前 1
SELECT subjects.name AS subject, subjects.id as subject_id, marks.CA AS ca, marks.exam AS exam,
marks.mark_obtained AS total, marks.percentages AS percentages, marks.student_id,
marks.subject_id, marks.class_id, marks.section_id, marks.session, marks.term, marks.school_id,
(SELECT grades.name FROM grades WHERE marks.mark_obtained >= grades.mark_from AND marks.mark_obtained <= grades.mark_upto AND grades.school_id=$school_id LIMIT 1) AS grade,
(SELECT grades.comment FROM grades WHERE marks.mark_obtained >= grades.mark_from AND marks.mark_obtained <= grades.mark_upto AND grades.school_id=$school_id LIMIT 1) AS remark
FROM users INNER JOIN students ON users.id = students.user_id
INNER JOIN marks ON students.id = marks.student_id
INNER JOIN subjects ON marks.subject_id = subjects.id
WHERE marks.school_id = ? AND marks.student_id = ? AND marks.session = ? AND marks.term = ? AND marks.class_id = ? AND marks.section_id = ?
ORDER BY marks.subject_id"
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/440910.html