Блог

У меня ниже 4 таблиц :

• Пользователь
• Студенты
• Отдел
• Отмечает

Я пытаюсь получить общее количество учащихся и сумму общих оценок по идентификатору отдела. Например, в моем результирующем наборе должно быть указано, что идентификатор отдела 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]
  

В моей таблице меток есть поля userId, DepartmentID, CollegeId, Value.

Например: — Если в таблице оценок есть 110 записей для DepartmentID 1 и 1 студент, который является студентом FTE, то в этом случае TotalUsers я получаю 110 всего студентов, хотя в этом отделе только 1 студент, потому что в отметках 110 записей, которые я получаю как 110 всего студентов

Есть ли более простой способ решить эту проблему?

Некоторые примеры данных и определения таблиц были бы полезны. Я изобрел свои собственные образцы данных, они должны почти соответствовать вашим определениям таблиц.

Использование cross apply или outer apply (документация и примеры) позволяет комбинировать результаты подсчета и суммирования.

Пример данных

 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;