SELECT
s.class_code,
COUNT(distinct s.student_id) AS num_students_90_plus,
ROUND(AVG(sc.score), 2) AS avg_score_90_plus,
t.name AS physics_teacher
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN teachers t ON t.class_code LIKE CONCAT('%', s.class_code, '%')
WHERE
sc.subject = '物理'
AND sc.score >= 90 and sc.exam_date='2024-06-30'
AND t.subject = '物理'
GROUP BY
s.class_code, t.name
ORDER BY
avg_score_90_plus DESC;
select
exam_date,
max(case when subject='语文' then score else null end) as chinese_score,
max(case when subject='数学' then score else null end) as math_score,
max(case when subject='英语' then score else null end) as english_score
from scores
where student_id='460093'
group by exam_date
select
case when enter_date>='2010-01-01' then '青年教师'
when enter_date>='2000-01-01' then '资深教师'
else '中年教师'
end as teacher_type,
count(*)
from teachers
group by teacher_type