需要SQL查询按班级导出学生出勤报告

Question

我有3个表:

<<ul>

学生表/gh>

<表类>id名称tbody><<tr>1乔恩2艾玛3奥利弗

参加出席两次，每堂课一次:

SELECT s.id, s.name
FROM students s 
JOIN attendance a1 ON a1.student_id = s.id and a1.class_id = 1
JOIN attendance a2 ON a2.student_id = s.id and a2.class_id = 2

或同时加入两个类，并通过with使用group:

SELECT s.id, s.name
FROM students s 
JOIN attendance a ON a.student_id = s.id and class_id in (1, 2)
GROUP BY s.id, s.name
HAVING COUNT(*) = 2

使用CTE会有所帮助，并且可以帮助您扩展到多个类。您还缺少一个DISTINCT关键字，这就是为什么Jhon出现了两次。也许像这样:

WITH attendance_count(student_id, classes_attended)
AS (
SELECT
student_id,
COUNT(id) AS classes_attended
FROM attendance a 
-- change this to change the classes
WHERE a.class_id IN (1,2)
GROUP BY student_id
)
SELECT DISTINCT s.id, s.name
FROM attendance_count 
INNER JOIN students s ON attendance_count.student_id = s.id
-- if different number of classes, change this
WHERE classes_attended = 2