Sql Server-使用具有更多记录的表进行连接和分组

Question

我有以下4张桌子：

< li >用户 <李>学生 < li >部门 < li >标记

我正在尝试按部门获取学生总数和总分。例如，我的结果集应该说部门Id 1有50名学生，所有学生在整个部门的所有考试中的总分数为1200。

当我需要部门总分时，下面的查询给出了正确的结果

SELECT DepartmentId, SUM([Value]) AS TotalMarks FROM [dbo].[Marks] M
WHERE CollegeId = 3
GROUP BY DepartmentId

当我只需要按系统计的学生总数时，下面的查询给出了正确的结果。

SELECT S.[DepartmentId], COUNT(U.[StudentId]) AS TotalStudents
FROM [dbo].User U
INNER JOIN dbo.[Student] S
ON U.[UserId] = S.[UserId]
INNER JOIN [dbo].Department D
ON D.[DepartmentId] = S.[DepartmentID]
WHERE D.[CollegeId] = 3 AND U.[IsFullTimeStudent] = 1
GROUP BY S.[DepartmentId]

现在，当我想使用下面的查询在一个结果中获得按部门划分的学生总数和总分时，我遇到了问题。我的分数表可以为单个用户提供多个记录，因此它会给出冗余的结果。

   SELECT S.[DepartmentId], COUNT(U.[StudentId]) AS TotalStudents, SUM(M.[Value]) AS TotalMarks
   FROM [dbo].User U
   INNER JOIN dbo.[Student] S
   ON U.[UserId] = S.[UserId]
   INNER JOIN [dbo].Department D
   ON D.[DepartmentId] = S.[DepartmentID]
   INNER JOIN [dbo].[Marks] M
   ON D.[DepartmentId] = M.[DeprtmentId]
   WHERE D.[CollegeId] = 3 AND U.[IsFullTimeStudent] = 1
   GROUP BY S.[DepartmentId]

我的标记表有用户 ID、部门 ID、学院 ID、值字段。

对于Ex ：- 如果分数表中的部门Id 1有110个条目，并且有1个FTE学生的学生，那么在这种情况下，TotalUsers我得到了110个学生总数，尽管该部门只有1个学生，因为分数中有110个条目，我得到的是110个学生总数

有没有更简单的方法来解决这个问题？

Answer 1

一些示例数据和表定义会很有用。我发明了自己的示例数据，它几乎应该符合您的表定义。

使用交叉应用或外部应用（留档和示例）允许合并计数和求和结果。

抽样资料

create table departments
(
  departmentid int,
  departmentname nvarchar(20)
);
insert into departments (departmentid, departmentname) values
(1000, 'Business Faculty'),
(2000, 'Science Faculty' ),
(3000, 'Maintenance'     );

create table users
(
  departmentid int,
  userid int,
  username nvarchar(10),
  isfulltimestudent bit
);
insert into users (departmentid, userid, username, isfulltimestudent) values
(1000, 1, 'Alice',    1),
(1000, 2, 'Bob',      0),
(2000, 3, 'Clarence', 1),
(2000, 4, 'Britt',    0);

create table students
(
  userid int,
  studentid int
);
insert into students (userid, studentid) values
(1, 100),
(2, 200),
(3, 300);

create table marks
(
  departmentid int,
  userid int,
  mark int
);
insert into marks (departmentid, userid, mark) values
(1000, 1, 15),
(1000, 1,  8),
(1000, 2, 13),
(1000, 2, 12),
(2000, 3, 10),
(2000, 3,  7),
(2000, 3, 15),
(2000, 4, 10);

解决办法

select d.departmentname,
       ts.TotalStudents,
       tm.TotalMarks
from departments d
outer apply ( select count(1) as TotalStudents
              from users u
              where u.departmentid = d.departmentid
                and u.isfulltimestudent = 1 ) ts
outer apply ( select sum(m.mark) as TotalMarks
              from marks m
              where m.departmentid = d.departmentid ) tm;

小提琴看它在起作用。

应用解决方案

未经测试的查询，用于合并问题中的查询：

SELECT d.DepartmentId,
       tm.TotalMarks,
       ts.TotalStudents
FROM dbo.Department d
OUTER APPLY ( SELECT SUM(m.[Value]) AS TotalMarks
              FROM dbo.Marks m
              WHERE m.DepartmentId = d.DepartmentId ) tm
OUTER APPLY ( SELECT COUNT(u.StudentId) AS TotalStudents
              FROM dbo.User u
              JOIN dbo.Student s
                ON u.UserId = s.UserId
              WHERE u.IsFullTimeStudent = 1
                AND s.DepartmentId = d.DepartmentId ) ts
WHERE d.CollegeId = 3;