Блог

Главная - Вопросы по программированию - Можно ли использовать агрегат в агрегате для получения определенного отдельного значения?

Можно ли использовать агрегат в агрегате для получения определенного отдельного значения?

#tsql

#tsql

Вопрос:

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

Вот мой код и ожидания

 SELECT C.Course,S.Name, COUNT(*) as Average FROM Students S
INNER JOIN Student_Modules SM ON
SM.StudentID = S.ID
INNER JOIN Courses_Template C
ON C.ID = SM.CourseID
Group by C.Course,S.Name
Having AVG(SM.Percentage_Obtained) > 80

Это возвращает мне строки, содержащие название курса, имя студента и количество процентов выше 80%.

Для меня это считается «количеством студентов, прошедших курс». Я хотел бы знать, как заставить этот запрос предоставить мне количество студентов, которые прошли курс, вместо количества модулей, которые прошел студент, и если это возможно

РЕДАКТИРОВАТЬ 1:

МАКЕТ УЧАЩЕГОСЯ

 CREATE TABLE Students
            (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
            ,StudentNumber VARCHAR(20)
            ,Name VARCHAR(40)
            ,Surname VARCHAR(40)
            ,Student_ID VARCHAR(13)
            ,Languages VARCHAR(200)
            ,[Address] Varchar (512)
            ,Contact_Number varchar(20)
            ,Email Varchar (150)
            ,Days_Absent INT
            ,Student_Web_Username varchar(40)
            ,Student_Web_Password varchar(MAX)
            ,BranchID int 
            ,Constraint FKStudentBranch FOREIGN KEY (BranchID) REFERENCES Branches(ID)
            ,CONSTRAINT Unq_StudentNumber UNIQUE (StudentNumber)
            ,CONSTRAINT Unq_Student_ID UNIQUE (Student_ID));

МАКЕТ STUDENT_MODULE

 CREATE TABLE Student_Modules
            (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
            ,ModuleID INT
            ,StudentID INT
            ,CourseID INT
            ,Percentage_Obtained INT Check (Percentage_Obtained >= -1 AND Percentage_Obtained <= 100)
            ,CONSTRAINT FKStudentModulesChosen FOREIGN KEY (ModuleID) REFERENCES Modules_Template(ID) ON DELETE CASCADE
            ,CONSTRAINT FKStudentModules FOREIGN KEY (StudentID) REFERENCES Students(ID) ON DELETE CASCADE);

МАКЕТ COURSES_TEMPLATE

 CREATE TABLE COURSES_TEMPLATE
        (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
        ,Course VARCHAR(40)
        ,Price SMALLMONEY CHECK(Price > 0)
        ,BranchID INT
        ,CONSTRAINT FKCourseBranches FOREIGN KEY (BranchID) REFERENCES Branches(ID) ON DELETE CASCADE);

Комментарии:

1. Можете ли вы также показать свою структуру таблицы? Не так-то просто вывести это из вашего запроса.

2. Я вставлю то, что смогу. хотя довольно большой макет

3. Все, что нам нужно, это поля, относящиеся к запросу. Я вижу только 3 таблицы и 5 или 6 полей в списке.

Ответ №1:

Если им нужно пройти в среднем 80% по всем модулям. 

 SELECT C.Course, COUNT(*) as [Average] 
FROM Students S 
INNER JOIN Student_Modules SM ON S.ID = SM.StudentID 
INNER JOIN Courses_Template C ON SM.CourseID = C.ID 
INNER JOIN (
    SELECT SM.StudentID, SM.CourseID 
    FROM Student_Modules SM 
    Group by SM.StudentID, SM.CourseID 
    Having AVG(SM.Percentage_Obtained) > 80
) Pass ON SM.StudentID = Pass.StudentID AND SM.CourseID = Pass.CourseID 
GROUP BY C.Course

Если им нужно пройти каждый модуль на 80%, чтобы пройти курс, тогда

 SELECT C.Course, COUNT(*) as [Average] 
FROM Students S 
INNER JOIN Student_Modules SM ON S.ID = SM.StudentID 
INNER JOIN Courses_Template C ON SM.CourseID = C.ID 
LEFT OUTER JOIN (
    SELECT DISTINCT SM.StudentID, SM.CourseID 
    FROM Student_Modules SM 
    WHERE SM.Percentage_Obtained <= 80
) as NotPass ON SM.StudentID = NotPass.StudentID AND SM.CourseID = NotPass.CourseID 
WHERE NotPass.StudentID IS NULL 
GROUP BY C.Course

Это не проверено, дайте мне знать о любых ошибках или вставьте неверный вывод и ожидаемый результат.

Комментарии:

1. Для меня это сработало достаточно хорошо, хотя я полностью переработал таблицу курсов для студентов, чтобы содержать битовое поле для пометки true, когда все связанные модули с этой таблицей (по словам студента) превышают 80%. снова молодец

Ответ №2:

Похоже, вам нужно количество студентов, прошедших каждый курс? Если да, то разве вам не нужно просто сгруппировать по C.Конечно, а затем подсчитать (S.Name ) как NumWhoPassed для отображения?

Комментарии:

1. вот некоторые псевдо. Количество (курс, студент, количество (СРЕДНЕЕ значение (SM.Percentage_Obtained> 80)))

2. Итак, если вы удалите S.Name это дает вам общее количество пройденных модулей за курс, а не количество пройденных студентов? Я, должно быть, заржавел, потому что это не так, как я думаю, что это пойдет. Я не могу придумать другого способа сделать это с головы до ног: ( извините.

3. все в порядке. не беспокойтесь об этом. Мне удалось отобразить его «Для каждого модуля», но это все еще не то, что мне нужно