mysql - 如何撤回哪些学生成绩全为 4？

Question

我有 400 多名学生，他们的类(class)分数范围为 1-4。学生可以选修 3 至 6 门类(class)。我怎样才能通读数据并只提取所有成绩均为“4”的学生。我正在考虑某种类型的排名功能，但我没有想到。

我有这段代码，它构建了一个临时表，并为我提供了每个学生的列表(以及其他数据)以及每个年级的分数。现在，我需要一次浏览一行并找出那些所有类(class)都只有 4 分的学生。

SELECT *
INTO #TempTableHonors
FROM
(
SELECT id.lastName + ', ' + id.firstName + COALESCE(' ' + LEFT(id.middleName,1),'') AS 'student'
, p.stateID
, cust.value AS RTAdvisor
, en.grade
, cs.name as ClassName
, gs1.score
, gt1.name
FROM Enrollment en 
    JOIN Person p on en.personID = p.personID
    JOIN [Identity] id on p.currentIdentityID = id.identityID and p.personID = id.personID
    JOIN Trial tr ON tr.calendarID = en.calendarID AND tr.active = 1
    JOIN Roster rs ON rs.personID = en.personID AND rs.trialID = tr.trialID
    JOIN Section sc ON sc.sectionID = rs.sectionID AND sc.trialID = tr.trialID
    JOIN Course cs ON cs.courseID = sc.courseID AND cs.calendarID = en.calendarID
    JOIN ScheduleStructure ss ON ss.calendarID = en.calendarID
    JOIN TermSchedule ts ON ts.structureID = ss.structureID
    JOIN Term tm ON tm.termScheduleID = ts.termScheduleID AND tm.seq = 1
    JOIN (GradingScore gs1 JOIN GradingTask gt1 ON gt1.taskID = gs1.taskID)
        ON gs1.calendarID = en.calendarID AND gs1.personID = en.personID AND gs1.sectionID = sc.sectionID AND gs1.termID = tm.termID
    LEFT JOIN customstudent cust on cust.personID = p.personID and cust.attributeID = 321
WHERE 1=1 
    AND en.calendarID = 1054
    AND en.serviceType = 'P'
   -- AND en.endDate is null
    AND (gt1.name = 'Quarter - Habits of Work')
group by id.lastName + ', ' + id.firstName + COALESCE(' ' + LEFT(id.middleName,1),'')
, p.stateID
, cust.value
, en.grade
, cs.name
, gs1.score
, gt1.name
) AS x

Answer 1

一种方法:

SELECT student
  FROM ( ... query that returns students and score ... )
 GROUP
    BY student
HAVING MIN(score) = 4
;

因此，例如，如果返回学生和分数的 ... 查询 ... 部分返回如下内容:

 student | score
---------+-------
 Jim     |     4
 Jim     |     3
 Kara    |     4
 Kara    |     4

那么整个查询将返回:

 student
---------
 Kara