SELECT
student_id,
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS avg_score
FROM scores
WHERE student_id IN (460093, 735011) AND subject = '数学'
GROUP BY student_id
SELECT
student_id,
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS AVG_score
FROM scores
WHERE student_id='460093'
AND subject='数学'
union
SELECT
student_id,
MAX(score) AS max_score,
MIN(score) AS min_score,
AVG(score) AS AVG_score
FROM scores
WHERE student_id='735011'
AND subject='数学'
SELECT
CASE WHEN YEAR(enter_date)>=2010 THEN '青年教师'
WHEN YEAR(enter_date)<2000 THEN '资深教师'
ELSE '中年教师' END AS teacher_type,
COUNT(teacher_id) AS teacher_count
FROM teachers
GROUP BY teacher_type
SELECT
student_id,
MAX(CASE WHEN subject='语文' THEN score ELSE 0 END)+
MAX(CASE WHEN subject='数学' THEN score ELSE 0 END)+
MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) AS total_score
FROM scores
WHERE exam_date='2024-06-30' AND subject IN('语文','数学','英语')
GROUP BY student_id
HAVING total_score>330
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
LEFT JOIN students USING(student_id)
WHERE student_id='460093'
AND subject IN ('语文','数学','英语')
GROUP BY exam_date
ORDER BY exam_date ASC