棘手的统计数据选择

Question

想弄清楚这件事有一段时间了，但没有运气。我有以下表格(MS 2008)：students

studentID -电子邮件- profileID

课程

courseID -名称

studentsCourses

studentID - courseID

配置文件

profileID -名称

profilesMandatoryCourses

profileID - courseID

studentsCoursesLogs

logID - studentID -courseID- accessDate

每个学生在入学时都会被分配一个个人资料。对于每个配置文件，都有一些必修课。这些必修课以及用户参加的任何其他课程都保存在studentsCourses表中。

每当学生访问一门课程时，这些信息都会记录在studentsCoursesLogs中。

我试图找出所有的学生谁已经参加了所有的必修课，根据他们的个人资料。

任何指南针都会赏识。

Answer 1

我认为这应该可以做到(假设我正确地理解了您的数据模型和需求-在将这个示例SQL放在一起时，我没有犯任何错误-我没有在数据库中创建数据模型)。不过，我很确定它应该够近的。

select studentRecords.StudentId, 
       sum(case TakenCourseID when null then 0 else 1 end) as CompleteMandatoryCourses,
       sum(case TakenCourseID when null then 1 else 0 end) as IncompleteMandatoryCourses
  from (
          select mandatoryCourses.StudentID, mandatoryCourses.CourseId as MandatoryCourseID, takenCourses.CourseID as TakenCourseID
            from ( -- Courses student should have taken - based on their profile
                   select p.[Profile], pmc.CourseID, s.StudentID 
                     from profiles p 
                     inner join profilesMandatoryCourses pmc on p.ProfileID = pmc.Profile
                     inner join students s on p.StudentID = s.StudentID
                 ) mandatoryCourses
            left join
                 (
                   -- Course students have taken
                   select s.StudentID, s.ProfileID, sc.CourseID
                     from students s
                     inner join studentsCourseLogs scl on s.StudentID = scl.StudentID
                 ) takenCourses on mandatoryCourses.ProfileId = takenCourses.ProfileID
                               and mandatoryCourses.CourseID = takenCourses.CourseID
       ) studentRecords
group by mandatoryCourse.StudentId
having sum(case TakenCourseID when null then 1 else 0 end) = 0

它将提供如下所示的记录集.

 +----------------+-----------------------------+-------------------------------+
 |  StudentID     |  CompleteMandatoryCourses   |  IncompleteMandatoryCourses   |
 +----------------+-----------------------------+-------------------------------+
 |      1         |      15                     |        3                      |
 |      2         |       8                     |        0                      |
 +----------------+-----------------------------+-------------------------------+

如果你只想要一张所有必修课学生的名单，你可以把上面所有的内容都包装起来，如下所示.

select studentID 
  from ( /* insert very long sql here */ ) 
 where IncompleteMandatoryCourses = 0`