MySQL基础训练50题之21~30

统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select s.c_id, (select count(1) from score where s_score between 0 and 60 and c_id=s.c_id)/count(1) "[0-60]" ,(select count(1) from score where s_score between 61 and 70 and c_id=s.c_id)/count(1) "[61-70]" ,(select count(1) from score where s_score between 71 and 85 and c_id=s.c_id)/count(1) "[71-85]" ,(select count(1) from score where s_score between 86 and 100 and c_id=s.c_id)/count(1) "[86-100]" from score s group by s.c_id

查询学生平均成绩及其名次

select r.s_id,r.平均成绩 "平均成绩",@rank:=@rank+1 "排名" from (select s_id,round(avg(s_score),1) "平均成绩" from score group by s_id order by 平均成绩 desc) r,(select @rank:=0) t

查询各科成绩前三名的记录

SELECT a.s_id,a.c_id,a.s_score FROM score a LEFT JOIN score b ON a.c_id = b.c_id AND a.s_score <= b.s_score GROUP BY a.s_id,a.c_id,a.s_score HAVING COUNT( b.s_id ) <= 3 ORDER BY a.c_id,a.s_score DESC

查询每门课程被选修的学生数

SELECT COUNT(*) 每门课程被选修的数量 FROM score GROUP BY c_id

查询出只有两门课程的全部学生的学号和姓名

SELECT s.s_id,s.s_name from(SELECT s_id,COUNT(*) 数量 FROM score GROUP BY s_id) number INNER JOIN student s ON s.s_id=number.s_id WHERE number.数量=2

查询男生、女生人数

SELECT s_sex,COUNT(*) FROM student GROUP BY s_sex SELECT s_sex,COUNT(*) FROM student GROUP BY s_sex

查询名字中含有"风"字的学生信息

SELECT * from student WHERE s_name LIKE '%风%'

查询同名同性学生名单，并统计同名人数

select s_name,count(*) as NUM from student group by s_name having count(*)>1

查询1990年出生的学生名单

SELECT * FROM student WHERE YEAR(s_birth)=1990

查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT c_id,AVG(s_score) a FROM score GROUP BY c_id ORDER BY a DESC,c_id ASC;