SQL - 从多个表中选择计数

Question

这是我的问题（我使用的是 SQL Server），

我有一个学生表（StudentId、Firstname、Lastname 等）。

我有一个记录 StudentAttendance（StudentId、ClassDate 等） 的表格。

我记录其他学生活动（为简单起见，我在这里进行概括），例如 Papers 表 （StudentId、PaperId 等）。可能有 0 到 20 篇论文上交。同样，有一个名为 Projects（StudentId、ProjectId 等） 的表。与论文相同。

我想做的是为出勤率超过一定水平（比如 10 出勤率）的学生创建一个计数列表。像这样的事情：

ID    Name      Att    Paper Proj
123   Baker     23     0     2
234   Charlie   26     5     3
345   Delta     13     3     0

这就是我所拥有的：

select 
  s.StudentId,
  s.Lastname,
  COUNT(sa.StudentId) as CountofAttendance,
  COUNT(p.StudentId) as CountofPapers
from Student s
inner join StudentAttendance sa on (s.StudentId = sa.StudentId)
left outer join Paper p on (s.StudentId = p.StudentId)
group by s.StudentId, s.Lastname
Having COUNT(sa.StudentId) > 10
order by CountofAttendance

如果 CountofPaper 和连接（内部或左外部）到 Papers 表被注释掉，则查询工作正常。我统计了至少参加过 10 门课程的学生。

但是，如果我放入 CountofPapers 和连接，事情就会变得疯狂。通过左外连接，任何拥有论文的学生都只需在论文栏中显示他们的出勤计数。通过内部连接，出勤率和论文数量似乎会成倍增加。

需要并赞赏指导。

戴夫

Answer 1

查看使用公用表表达式，然后分而治之。顺便说一句，你在原来的查询中落后了 1，你的最低出席人数为 11，

;
WITH GOOD_STUDENTS AS
(
-- this query defines all students with 10+ attendance
SELECT
    S.StudentID
,   count(1) AS attendence_count
FROM
    Student S
    inner join 
    StudentAttendance sa 
    on (s.StudentId = sa.StudentId)
GROUP BY
    S.StudentId
HAVING
    COUNT(1) >= 10
)
,  STUDIOUS_STUDENTS AS
(
-- lather, rinse, repeat for other metrics
SELECT
    S.StudentID
,   count(1) AS paper_count
FROM
    Student S
    inner join 
    Papers P 
    on (s.StudentId = P.StudentId)
GROUP BY
    S.StudentId
)
,  GREGARIOUS_STUDENTS AS
(
SELECT
    S.StudentID
,   count(1) AS project_count
FROM
    Student S
    inner join 
    Projects P 
    on (s.StudentId = P.StudentId)
GROUP BY
    S.StudentId
)
-- And now we roll it all together
SELECT
    S.*
,   G.attendance_count
,   SS.paper_count
,   GS.project_count
-- ad nauseum
FROM
    -- back to the well on this one as there may be 
    -- students did nothing
    Students S
    LEFT OUTER JOIN
        GOOD_STUDENTS G
        ON G.studentId = S.studentId
    LEFT OUTER JOIN
        STUDIOUS_STUDENTS SS
        ON SS.studentId = S.studentId
    LEFT OUTER JOIN
        GREGARIOUS_STUDENTS GS
        ON GS.studentId = S.studentId

我看到很多其他答案滚滚而来，但我输入了太长时间而无法退出;)

Answer 2

问题是每个学生有多份论文，因此加入的每一行论文都有一个StudentAttendance行：计数将被重新添加每次。试试这个：

select 
  s.StudentId,
  s.Lastname,
  (select COUNT(*) from StudentAttendance where s.StudentId = sa.StudentId) as CountofAttendance,
  (select COUNT(*) from Paper where s.StudentId = p.StudentId) as CountofPapers
from Student s
where (select COUNT(*) from StudentAttendance where s.StudentId = sa.StudentId) > 10
order by CountofAttendance

编辑参考 CountofAttendance 合并问题

顺便说一句，这不是最快的解决方案，但它是最容易理解的，这就是我的意图。您可以通过使用别名选择的联接来避免重新计算，但正如我所说，这是最简单的。

Answer 3

试试这个：

select std.StudentId, std.Lastname, att.AttCount, pap.PaperCount, prj.ProjCount
from Students std
    left join
    (
        select StudentId, count(*) AttCount
        from StudentAttendance
    ) att on
        std.StudentId = att.StudentId
    left join
    (
        select StudentId, count(*) PaperCount
        from Papers
    ) pap on
        std.StudentId = pap.StudentId
    left join
    (
        select StudentId, count(*) ProjCount
        from Projects
    ) prj on
        std.StudentId = prj.StudentId
where att.AttCount > 10