Mysql关联查询，无数据的字段显示错误的数据

Question

下面是我查询的代码

SELECT
    `class_student`.`student_id` AS `student_id`,
    `class_student`.`class_id` AS `class_id`,
    `test_info`.`teacher_id` AS `teacher_id`,
    `class_info`.`class_name` AS `class_name`,
    `class_test`.`test_id` AS `test_id`,
    `test_info`.`test_name` AS `test_name`,
    `test_info`.`open_status` AS `open_status`,
    `test_info`.`open_time` AS `open_time`,
    `test_info`.`close_time` AS `close_time`,
    `test_info`.`time_limit` AS `time_limit`,
    `student_test_finish_record`.`start_time` AS `start_time`,
    `student_test_finish_record`.`finish_time` AS `finish_time` 
FROM
    ((((
                    `class_student`
                    JOIN `test_info` 
                    )
                JOIN `class_test` ON ((
                        `test_info`.`test_id` = `class_test`.`test_id` 
                    )))
            JOIN `class_info` ON (((
                        `class_student`.`class_id` = `class_info`.`class_id` 
                        ) 
                AND ( `class_test`.`class_id` = `class_info`.`class_id` ))))
        LEFT JOIN `student_test_finish_record` ON ((
            `test_info`.`test_id` = `student_test_finish_record`.`test_id` 
    )))

数据库的字段及联系

表关系

请问一下我这个问题怎么解决？

Answer 1

漏了两个关联条件

另外,你这sql没必要一层一层的套, 下面这种写法也是可以的

select * from a 
join b on a.xx=b.xx and a.yy=b.yy
join c on a.xx=c.xx

---

下面两种写法都可以

select t.*,
       student_test_finish_record.start_time,
       student_test_finish_record.finish_time
  from (select a.student_id,
               a.class_id,
               c.teacher_id,
               d.class_name,
               b.test_id,
               c.test_name,
               c.open_status,
               c.open_time,
               c.close_time,
               c.time_limit
          from class_student a, class_test b, test_info c, class_info d
         where a.class_id = d.class_id
           and d.class_id = b.class_id
           and b.test_id = c.test_id
           and c.teacher_id = d.teacher_id) as t
  left join student_test_finish_record e
    on t.test_id = e.test_id
   and t.student_id = e.student_id;

select t.*,
       student_test_finish_record.start_time  AS start_time,
       student_test_finish_record.finish_time AS finish_time
  from (select class_student.student_id AS student_id,
               class_student.class_id   AS class_id,
               test_info.teacher_id     AS teacher_id,
               class_info.class_name    AS class_name,
               class_test.test_id       AS test_id,
               test_info.test_name      AS test_name,
               test_info.open_status    AS open_status,
               test_info.open_time      AS open_time,
               test_info.close_time     AS close_time,
               test_info.time_limit     AS time_limit
          from class_student
          join class_info
            on class_student.class_id = class_info.class_id
          join class_test
            on class_info.class_id = class_test.class_id
           and class_test.test_id = test_info.test_id
          join test_info
            on test_info.teacher_id = class_info.teacher_id) as t
  left join student_test_finish_record
    on t.test_id = student_test_finish_record.test_id
   and t.student_id = student_test_finish_record.student_id;