mysql查询每门课的平均分 mysql查询课程的平均分

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

思路，也就是每个老师所教课程的平均分，那么以老师表为主表，关联课程表，因为关联的课程表是条件匹配，所以直接用inner join，课程表关联分数表，也是条件匹配，直接关联，用inner join，注意，分组的时候，题目说的是不同老师、不同课程、那么分组就要是： 老师id、课程id、课程名（因为可能出现课程id不一样，但课程名一样）

SELECT
	t.t_name,
	c.c_name,
	ROUND( avg( sc.s_score ), 2 ) 
FROM
	teacher t
	INNER JOIN course c ON c.t_id = t.t_id
	INNER JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
	t.t_id,
	c.c_id,
    c.t_name
ORDER BY
	avg( sc.s_score ) DESC

网上解法：

SELECT
	a.t_id,
	c.t_name,
	a.c_id,
	ROUND( avg( s_score ), 2 ) AS avg_score 
FROM
	course a
	LEFT JOIN score b ON a.c_id = b.c_id
	LEFT JOIN teacher c ON a.t_id = c.t_id 
GROUP BY
	a.c_id,
	a.t_id,
	c.t_name 
ORDER BY
	avg_score DESC;

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

思路：
1.先按c_id排序好的前提下，在c_id已经有序的前提下，在进行分数的排序

SELECT
	sc.s_id,
	sc.c_id,
	sc.s_score 
FROM
	score sc 
ORDER BY
	sc.c_id,
	sc.s_score DESC

2.接下来就是加上排名，排名的话则要使用自定义变量

SELECT
	sc.s_id,
	sc.c_id,
	sc.s_score ,
	-- 这里必须放在@c-d:=sc.cid上面，也就是如果我们遍历这一行的cid，@cid(保存是上一行的cid)，
	-- 不等于我门这一样的cid，也就是开始另外一门课程了，所以就重新为1
	@i:=(case when @cid = c_id then @i+1 when @cid != c_id then 1 END) as 排名,
	@cid:= sc.c_id AS c_id
	
FROM
	score sc , ( SELECT @i := 0,@cid=0 ) AS i
ORDER BY
	sc.c_id,
	sc.s_score DESC

3. 接下来我们只要关联到学生信息即可，并且按排名取前两位，这里用右连接，也就是右表为保留的数据，去匹配学生表的数据，并且条件就是排名=2或者=3的进行帅选出来即可

SELECT
	stu.*,
	a.排名,
	a.分数,
	a.c_id 
FROM
	student stu
	RIGHT JOIN (
		SELECT
			s_id,
			@i := ( CASE WHEN @cid = c_id THEN @i + 1 WHEN @cid != c_id THEN 1 END ) AS 排名,
			s_score AS 分数,
			@cid := c_id AS c_id 
		FROM
			score,
			( SELECT @i := 0, @cid := 0 ) s 
		ORDER BY
			c_id,分数 DESC 
	) a ON stu.s_id = a.s_id 
WHERE
	排名 BETWEEN 2 
	AND 3 
ORDER BY

1. c_id,分数 DESC;

统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
这里本来思路想到的的是各个分数段的人数，但想到的却总是表关联后，再关联，一看网上解法，发现还是牛逼呀，还是得慢慢学习练习，等找时间这个sql自己写一次；
这里的优点在于： 只要表关联一次即可，要学习的地方就是利用sum里面可以用ifelse方式，这一点我没想到；
注意：
if语法要加括号
sum(if(…条件., 1,0))表示如果条件成立，则输出1，否则输出0，然后将输出的进行求和
网上解法(sql优化比较好的)

SELECT 
	score.c_id AS 课程编号, 
	course.c_name AS 课程名称, 
	sum( IF ( score.s_score >= 85 AND score.s_score <= 100, 1, 0 ) ) AS '[100-85]人数', 
	100 * sum( IF ( score.s_score >= 85 AND score.s_score <= 100, 1, 0 ) ) / count( score.s_score ) AS '[100-85]百分比', 
	sum( IF ( score.s_score >= 70 AND score.s_score <= 85, 1, 0 ) ) AS '[85-70]人数', 
	100 * sum( IF ( score.s_score >= 70 AND score.s_score <= 85, 1, 0 ) ) / count( score.s_score ) AS '[85-70]百分比', 
	sum( IF ( score.s_score >= 60 AND score.s_score <= 70, 1, 0 ) ) AS '[70-60]人数', 
	100 * sum( IF ( score.s_score >= 60 AND score.s_score <= 70, 1, 0 ) ) / count( score.s_score ) AS '[70-60]百分比', 
	sum( IF ( score.s_score >= 0 AND score.s_score <= 60, 1, 0 ) ) AS '[0-60]人数', 
	100 * sum( IF ( score.s_score >= 0 AND score.s_score <= 60,1,0 ) ) / count( score.s_score ) AS '[0-60]百分比' 
FROM
	score score
	JOIN course course ON course.c_id = score.c_id 
GROUP BY
	score.c_id

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

– 思路： 先算出平均成绩，然后在利用自定义变量进行名次排名
平均成绩

SELECT
	st.s_name,
	st.s_id,
	avg( sc.s_score ) AS avg_score 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
ORDER BY
	avg_score DESC

在用自定义变量进行排名

SELECT
	T.*,
	@i := @i + 1 AS "排名" 
FROM
	(
		SELECT
			st.s_name,
			st.s_id,
			avg( sc.s_score ) AS avg_score 
		FROM
			student st
			LEFT JOIN score sc ON sc.s_id = st.s_id 
		GROUP BY
			st.s_id 
		ORDER BY
			avg_score DESC 
	) T,

( SELECT @i := 0 ) s

网上解法

这个解法是把为空的也可以排除掉，采用了case…when的方式进行

SELECT
	a.s_id,
	@i := @i + 1 AS '不保留空缺排名',
	@k := ( CASE WHEN @avg_score = a.avg_s THEN @k ELSE @i END ) AS '保留空缺排名',
	@avg_score := avg_s AS '平均分' 
FROM
	( SELECT s_id, ROUND( AVG( s_score ), 2 ) AS avg_s FROM score GROUP BY s_id ORDER BY avg_s DESC ) a,
	( SELECT @avg_score := 0, @i := 0, @k := 0 ) b;

25、查询各科成绩前三名的记录(重点知识)

结果，这个分析过程我专门写了一篇文章分析，可以参考一下：查询各科成绩前三名的记录

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

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

思路：也就是c_id为分组，统计学生人数

select c_id, count(s_id) from score group by c_id

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

思路:只有两门课程，则我们可以在课程表中根据学生进行分组，然后统计课程id数，并且过滤出课程数=2门的学生，即得出结果。
然后在关联学生表，学生学号和姓名则得出

SELECT
	st.s_name,
	sc.s_id,
	count( sc.c_id ) AS "课程数" 
FROM
	score sc
	INNER JOIN student st ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id 
HAVING
	count( sc.s_id ) = 2

28、查询男生、女生人数

思路： 用sum函数即可解决，里面再用case when做判断即可

SELECT
	sum( CASE WHEN s_sex = "男" THEN 1 ELSE 0 END ) AS "男生数",
	sum( CASE WHEN s_sex = "女" THEN 1 ELSE 0 END ) AS "女生数" 
FROM
	student

网上解法：

select s_sex,COUNT(s_sex) as 人数  from student GROUP BY s_sex

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

SELECT
	st.* 
FROM
	student st 
WHERE
	st.s_name LIKE "%风%"

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

思路：这个在学生表，则同名同姓，前提是主键不能一样，即两个学生表关联，然后关联的id不能一样，条件是名字一样，且性别也一样

SELECT
	st1.s_name,
	count( st1.s_name ) 
FROM
	student st1
	INNER JOIN student st2 ON st2.s_id != st1.s_id 
WHERE
	st1.s_name = st2.s_name 
	AND st1.s_sex = st2.s_sex 
GROUP BY
	st1.s_name,
	st1.s_sex

网上解法

SELECT
	a.s_name,
	a.s_sex,
	count( * ) 
FROM
	student a
	JOIN student b ON a.s_id != b.s_id 
	AND a.s_name = b.s_name 
	AND a.s_sex = b.s_sex 
GROUP BY
	a.s_name,
	a.s_sex