Только студенты с лучшей оценкой
У меня простая связь между учеником и оценками:
Ученик :
+------------+-------------+
| idStudent | NameStudent |
+------------+-------------+
| 1 | Student A |
| 2 | Student B |
| 3 | Student C |
+------------+-------------+
Метки :
+----------+------+-----------+
| idMarks | mark | idStudent |
+----------+------+-----------+
| 1 | A | 1 |
| 2 | A | 1 |
| 3 | A | 1 |
| 4 | A | 2 |
| 5 | A | 2 |
| 6 | C | 3 |
| 7 | A | 3 |
+----------+------+-----------+
Я хочу, чтобы учащиеся получали пятерки за каждый сданный экзамен.
SELECT *, COUNT(mark)
FROM student S
INNER JOIN marks M ON S.idStudent = M.idStudent
WHERE M.mark = "A"
GROUP BY S.idStudent
Я пробовал это, но захожу в тупик, когда пытаюсь сравнить количество сданных ими экзаменов и количество A, которые у них есть ...
Я также пробовал использовать подзапросы, но это не сработало
Ответы 3
- Группировать по студенту
- Найдите все отличные оценки, полученные учеником, и поместите их в строку, разделенную запятыми, с помощью функции Group_concat
- Просто отфильтруйте (используя предложение Имея) те, которые имеют только одну общую отличительную метку, то есть А
Используйте следующий запрос:
SELECT S.idStudent,
S.NameStudent,
GROUP_CONCAT(DISTINCT M.mark) AS unique_marks
FROM student S
INNER JOIN marks M ON S.idStudent = M.idStudent
GROUP BY S.idStudent, S.NameStudent
HAVING unique_marks = 'A'
@ZeyukanIch 'Лично я считаю, что это очень удобный подход во многих сценариях.
Вы можете использовать HAVING:
SELECT S.idStudent
FROM student S
INNER JOIN marks M ON S.idStudent = M.idStudent
GROUP BY S.idStudent
HAVING COUNT(mark)=SUM(mark='A');-- compare all marks with marks that are only 'A'
-- if equal then every mark is 'A'
Если вам просто нужны идентификаторы учащихся, вы можете выставить оценки учащимся LEFT JOIN, включая условие в предложении соединения для метки, отличной от A. Тогда в предложение WHERE будут включены только учащиеся с несовпадающими строками:
SELECT Student.idStudent
FROM Student
LEFT JOIN Marks BelowA ON
BelowA.idStudent = Student.idStudent
AND BelowA.mark <> 'A'
WHERE BelowA.idStudent IS NULL
@LukaszSzozda: Я тестировал, и это не так. Если учащийся имеет только As, то запрос вернет 1 строку для этого учащегося из-за условий LEFT JOIN и WHERE. С данными примера OP студент A (idStudent=1) имеет все A и только одну запись в моем запросе.
Хорошо, а что со случаем, когда у Студента нет оценок?
@LukaszSzozda --- ну, вы можете придраться к семантике и сказать, что такой студент соответствует требованиям (для этого студента нет экзамена, на котором он не сделал получил бы A). Вы также можете добавить еще один критерий для исключения этих студентов (например, EXISTS (SELECT * FROM Marks WHERE idStudent = Students.idStudent))) или INNER JOIN из всей таблицы Marks (что, как вы указываете, требует наличия пункта DISTINCT).
этот подход кажется немного сложнее, но намного интереснее