1,一张学生的name表,包含id,和name,如下图
2.一张分数表包含学生id和分数score,如下图
3.查询出大于平均分数的学生的名字和性别和分数,按分数降序排序;
SELECT id_n, name_n, score, sex FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1
WHERE score>(SELECT AVG(score) FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1)
ORDER BY score DESC;
4.查询出大于平均分数的男女生的人数:
SELECT sex as “性别”, COUNT(*) FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1
WHERE score>(SELECT AVG(score) FROM `name_table` a INNER JOIN score_table b ON a.id_n=b.id_n1) GROUP BY sex;