棘手的统计数据选择

Question

有一段时间想弄明白，但运气不好。我有以下表格（MS-SQL 2008）：学生ID–电子邮件–档案ID

课程
courseID–名称

学生资源
学生ID–课程ID

配置文件

课程设置

学生学习日志
logID-studentID-courseID-accessDate

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

每当学生访问课程时，信息都会记录在学生浏览日志中。

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

欢迎指点。

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`