怎样才能让哪些学生在成绩上获得全部4分呢？

Question

我有400+学生，他们的课程成绩范围为1-4 .学生可以选修3-6门课程。我怎样才能阅读这些数据，而只把那些在所有成绩上得了'4‘的学生拉回来。我在考虑某种排序功能，但这不是我想要的。

我有这样的代码，它构建了一个临时表，并给出了每个学生的列表(连同其他数据)以及他们每个年级的分数。现在，我需要一次一排地复习，找出那些只有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
;

因此，例如，如果... query that returns students and score ...部件返回如下内容：

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

然后，整个查询将返回以下内容：

 student
---------
 Kara

Answer 2

有几种方法可以解决这个问题。一种方法是：

想象一张桌子

+---------+--------+-------+
| student | course | grade |
+---------+--------+-------+
|       1 | a      |     4 |
|       1 | b      |     4 |
|       1 | c      |     3 |
|       1 | d      |     4 |
|       2 | a      |     4 |
|       2 | b      |     4 |
|       2 | d      |     4 |
|       3 | a      |     4 |
|       3 | b      |     4 |
|       3 | c      |     4 |
|       3 | d      |     3 |
+---------+--------+-------+

1. 抓取所有有记录但不是4分的学生： SELECT student FROM grades WHERE grade <> 4 GROUP BY student;
2. 然后找一个不在名单上的学生名单 SELECT student FROM grades WHERE student NOT IN (SELECT student FROM grades WHERE grade <> 4 GROUP BY student);