数据库表格 1 查询出各科平均值 比张三高的学生名称 与平均成绩 2 求出 与 张三 一个班的所有学生 3 求出每个班的男人人数和女生人数 4 求出体育成绩大于60分的学生姓名 并 降序排序 5 查询出学生成绩比李1 体育成绩高的男生 和 低的女生

1. 查询出各科平均值比张三高的学生名称与平均成绩：

SELECT s.name, AVG(sc.score) as avg_score
FROM student s
JOIN score sc ON s.id = sc.student_id
WHERE sc.subject_id = 'subject_id'
GROUP BY sc.subject_id
HAVING AVG(sc.score) > (
    SELECT AVG(sc2.score) 
    FROM student s2 
    JOIN score sc2 ON s2.id = sc2.student_id
    WHERE s2.name = '张三'
    AND sc2.subject_id = 'subject_id'
)

2. 求出与张三一个班的所有学生：

SELECT s2.name 
FROM student s 
JOIN class c ON s.class_id = c.id 
JOIN student s2 ON s2.class_id = c.id 
WHERE s.name = '张三'

3. 求出每个班的男人人数和女生人数：

SELECT c.name, 
       COUNT(CASE WHEN s.gender = '男' THEN 1 ELSE NULL END) as male_count, 
       COUNT(CASE WHEN s.gender = '女' THEN 1 ELSE NULL END) as female_count
FROM class c
JOIN student s ON c.id = s.class_id
GROUP BY c.id

4. 求出体育成绩大于60分的学生姓名并降序排序：

SELECT s.name 
FROM student s 
JOIN score sc ON s.id = sc.student_id 
WHERE sc.subject_id = '体育' 
AND sc.score > 60 
ORDER BY sc.score DESC

5. 查询出学生成绩比李1体育成绩高的男生和低的女生：

SELECT s.name 
FROM student s 
JOIN score sc ON s.id = sc.student_id 
WHERE sc.subject_id = '体育' 
AND sc.score < (
    SELECT sc2.score 
    FROM student s2 
    JOIN score sc2 ON s2.id = sc2.student_id 
    WHERE s2.name = '李1' 
    AND sc2.subject_id = '体育'
) 
AND s.gender = '男'
UNION 
SELECT s.name 
FROM student s 
JOIN score sc ON s.id = sc.student_id 
WHERE sc.subject_id = '体育' 
AND sc.score > (
    SELECT sc2.score 
    FROM student s2 
    JOIN score sc2 ON s2.id = sc2.student_id 
    WHERE s2.name = '李1' 
    AND sc2.subject_id = '体育'
) 
AND s.gender = '女'