查询错误

Question

SELECT Student.S_ID, Student.First_Name, Student.Surname, 
       MAX(New_Models.Date_Issued) AS Last_Course_Date, 
       MAX(New_Models.Issue) AS Last_Issue, 
       MAX(New_Models.Model_ID) AS Last_Model_ID,
       Student.Course_Level AS No_Training_Courses,
       **COUNT(Exam_Student.Exam_ID) AS Final_Exam_Level**
FROM New_Models, Model_Student, Student, Exams, Exam_Student
WHERE New_Models.Model_ID=Model_Student.Model_ID 
  AND Student.S_ID=Model_Student.S_ID
  AND (Student.S_ID)=Exam_Student.S_ID
  AND ((Exams.Exam_ID)=Exam_Student.Exam_ID)
  AND (Exams.Date_Taken)<=#12/31/2010#
GROUP BY Student.S_ID, First_Name, Surname, S.Course_Level
ORDER BY Student.S_ID, MAX(New_Models.Model_ID) DESC;

除 COUNT 命令外，所有结果均按要求显示。当与应该出现的实际结果进行比较时，只有第一个单元格不同并且是错误的。 应该计算学生完成了多少次考试。

我可以回答任何问题，等待您的帮助。

SELECT 
    Student.S_ID, 
    Student.first_name, 
    Student.surname, 
    MAX(new_models.date_issued) AS last_course_date, 
    MAX(new_models.issue) AS last_issue, 
    MAX(new_models.model_id) AS last_model_id, 
    Student.course_level AS no_training_courses 
FROM 
    new_models, 
    Student, 
    Model_Student 
WHERE 
    new_models.model_id = Model_Student.model_id 
    AND Student.S_id = Model_Student.S_id 
    AND *strong text*Exam_ID IN ( 
            SELECT COUNT(Exam_Student.Exam_id) AS Final_Exam_Level 
            FROM 
                Exams, 
                Exam_Student, 
                Student 
            WHERE 
                Student.S_ID = Exam_Student.S_ID 
                AND exams.date_taken <=#12/31/2010# 
            Group by Student.S_ID) 
GROUP BY 
    Student.S_ID,
    first_name, 
    surname, 
    Student.Course_level 
ORDER BY 
    Student.S_id, 
    MAX(new_models.model_id) DESC;

我不能使用 INNER JOIN 所以，我想知道可以完成类似上面的事情。但是，我不知道如何进行子查询！

Answer 1

一种可能有帮助的方法是简单地在计数上创建一个子查询，然后连接到该子查询。

我还建议使用 ANSI-92 样式连接

SELECT student.s_id, 
       student.first_name, 
       student.surname, 
       MAX(new_models.date_issued) AS last_course_date, 
       MAX(new_models.issue)       AS last_issue, 
       MAX(new_models.model_id)    AS last_model_id, 
       student.course_level        AS no_training_courses,
       Examqry.ExamCount 
FROM   new_models 
       INNER JOIN model_student ON  new_models.model_id = model_student.model_id 
       INNER JOIN student ON student.s_id = model_student.s_id 
       INNER JOIN (SELECT COUNT(Exam_id) ExamCount , 
                                         s_id 
                           FROM exams Group by s_id
                          Where exams.date_taken <=#12/31/2010#) examqry
       ON student.s_id = examqry.s_id

GROUP  BY student.s_id, 
          first_name, 
          surname, 
          s.course_level 
ORDER  BY student.s_id, 
          MAX(new_models.model_id) DESC; 

Answer 2

我猜测没有看到示例数据，但也许可以尝试这个，更改

AND (Student.S_ID)=Exam_Allocation.S_ID
AND ((Exams.Exam_ID)=Exam_Allocation.Exam_ID)

为

AND Student.S_ID=Exams.Exam_ID
AND Exams.Exam_ID=Exam_Allocation.Exam_ID

如果这没有帮助，您将需要发布一些示例数据以及预期和实际结果以供某人帮助。