mysql sql语句面试经典50题_MySQL经典50题的解法（21—35）

21、查询不同老师所教不同课程平均分从高到低显示

SELECT c.t_id, t_name, c_name, AVG(s_score) avg_score

FROM course a

LEFT JOIN score b   ON b.c_id=a.c_id

LEFT JOIN teacher c ON c.t_id=a.t_id

GROUP BY c.t_id

ORDER BY avg_score DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT *

FROM (

SELECT (@i := CASE WHEN @j = c_id THEN @i+1 ELSE 1 END) rank,

(@j := c_id) c_id, c_name, s_id, s_name, s_birth, s_sex, s_score

FROM (SELECT @i := 0, @j := 1) var

CROSS JOIN

( SELECT c.c_id,c.c_name,a.*,s_score

FROM student a

LEFT JOIN score b   ON b.s_id=a.s_id

LEFT JOIN course c  ON c.c_id=b.c_id

GROUP BY c.c_id,a.s_id

ORDER BY c.c_id,s_score DESC

)t1

)t2

WHERE rank IN(2,3)

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

SELECT c.c_id `课程编号`, c.c_name `课程名称`,

SUM(CASE WHEN s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) `[100-85]`,

CONCAT(ROUND(SUM(CASE WHEN s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') `百分比`,

SUM(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) `[85-70]`,

CONCAT(ROUND(SUM(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') `百分比`,

SUM(CASE WHEN s_score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) `[70-60]`,

CONCAT(ROUND(SUM(CASE WHEN s_score BETWEEN 60 AND 70 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') `百分比`,

SUM(CASE WHEN s_score BETWEEN 0 AND 60 THEN 1 ELSE 0 END) `[100-85]`,

CONCAT(ROUND(SUM(CASE WHEN s_score BETWEEN 0 AND 60 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') `百分比`

FROM course c

LEFT JOIN score sc ON sc.c_id=c.c_id

GROUP BY c.c_id

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

SELECT(@i := @i+1) rank,t1.*

FROM (SELECT @i := 0) var

CROSS JOIN (

SELECT s.*,ROUND(AVG(s_score),2) avg_score

FROM student s

LEFT JOIN score sc ON sc.s_id=s.s_id

GROUP BY s.s_id

ORDER BY avg_score DESC

) t1;

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

SELECT *

FROM

(SELECT (@i := CASE WHEN @j = c_id THEN @i+1 ELSE 1 END) rank, t1.*,

(@j := c_id) `课程编号`

FROM (SELECT @i := 0, @j := 1) var

CROSS JOIN(

SELECT c.c_id,c_name,s_score,s.*

FROM

student s

INNER JOIN score sc ON sc.s_id=s.s_id

INNER JOIN course c ON c.c_id=sc.c_id

ORDER BY c.c_id,s_score DESC

) t1

) t2

WHERE rank IN(1,2,3);

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

SELECT c.c_id,c_name,COUNT(*)

FROM score sc

INNER JOIN course c ON c.c_id=sc.c_id

GROUP BY c.c_id

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

SELECT s_id,s_name

FROM student

WHERE s_id IN (

SELECT s_id

FROM score

GROUP BY s_id

HAVING COUNT(*)=2

);

28、查询男生、女生人数

SELECT s_sex,COUNT(1) '人数'

FROM student

GROUP BY s_sex

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

SELECT *

FROM student

WHERE s_name LIKE '%风%'

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

SELECT *, COUNT(*)

FROM student

GROUP BY s_name,s_sex

HAVING COUNT(*)>1;

31、查询1990年出生的学生名单

SELECT *

FROM student

WHERE YEAR(s_birth)='1990'

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

SELECT sc.c_id, c_name, ROUND(AVG(s_score),2) avg_score

FROM score sc

LEFT JOIN course c ON c.c_id=sc.c_id

GROUP BY sc.c_id

ORDER BY avg_score DESC, sc.c_id

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT s.s_id 学号, s_name 姓名, AVG(s_score) 平均成绩

FROM student s

LEFT JOIN score sc ON s.s_id=sc.s_id

GROUP BY `学号`

HAVING `平均成绩` >= 85

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT s_name,c_name,s_score

FROM student s

LEFT JOIN score sc ON s.s_id=sc.s_id

LEFT JOIN course c ON c.c_id=sc.c_id

WHERE s_score<60

AND c_name='数学'

35、查询所有学生的课程及分数情况

SELECT s.s_id, s_name,

SUM(CASE c.c_id WHEN 1 THEN s_score ELSE 0 END) '语文',

SUM(CASE c.c_id WHEN 2 THEN s_score ELSE 0 END) '数学',

SUM(CASE c.c_id WHEN 3 THEN s_score ELSE 0 END) '英语'

FROM student s

LEFT JOIN score sc ON sc.s_id=s.s_id

LEFT JOIN course c ON c.c_id=sc.c_id

GROUP BY s.s_id