SQL -从多个表中选择计数

Question

这是我的问题(我使用的是SQL Server)

我有一桌Students (StudentId, Firstname, Lastname, etc)。

我有一个记录StudentAttendance (StudentId, ClassDate, etc.)的表

我记录其他学生活动(为了简单起见，我在这里泛化一下)，比如Papers表(StudentId, PaperId, etc.)。可能会有0到20篇论文上交。类似地，有一个名为Projects (StudentId, ProjectId, etc.)的表。与Papers的交易相同。

我要做的是为出勤率超过一定水平(比如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

如果注释掉了Papers表的CountofPaper和连接(内部或外部)，查询就能正常工作。我得到了至少上过10节课的学生的数量。

但是，如果我放入CountofPapers和join，事情就会变得疯狂。使用左外部连接，任何有论文的学生只在论文列中显示他们的出席率。使用内部连接时，出席率和论文数量似乎都会相互影响。

需要并感谢您的指导。

戴夫

Answer 1

问题是每个学生有多个papers，所以连接的每一行Paper都有一个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的问题

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

Answer 2

看看如何使用Common Table Expressions，然后分开解决你的问题。顺便说一句，您在原始查询中差了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 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