MySQL 基本查询27---33

题目27：查询不同老师所教不同课程平均分从高到低显示

SSELECT
	t2.t_name,
    t2.c_name,
	ROUND(AVG(t1.s_score), 2) AS avg_score
FROM
	score t1
JOIN (
	SELECT
		a.t_name,
		b.c_id,
    b.c_name
	FROM
		teacher AS a,
		course AS b
	WHERE
		a.t_id = b.t_id
) t2 ON t1.c_id = t2.c_id
GROUP BY t2.t_name, t2.c_name
ORDER BY ROUND(AVG(t1.s_score), 2) DESC;

注意： 1）上题中，最好将在select语句中除聚合函数外的列全部放到group by语句后； 2）上题中，由于select语句比order by语句先执行，所以在order by后必须使用ORDER BY ROUND(AVG(t1.s_score), 2) DESC，而不能采用别名avg_score；

题目28：查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

该题目采用union函数进行合并，但合并的数据必须包含相同的列；

SELECT
	s.s_id,
	s.s_name,
	t.c_name,
	t.s_score
FROM
	Student s
JOIN (
	-- union连接
	(
		SELECT
			s.s_id,
			s.s_score,
			c.c_name
		FROM
			Score s
		JOIN Course c ON s.c_id = c.c_id
		WHERE
			c.c_name = '语文'
		ORDER BY
			s.s_score DESC
		LIMIT 1,2 -- 从第1行数据之后取2行，即取第2,3行数据
	)
	UNION
		(
			SELECT
				s.s_id,
				s.s_score,
				c.c_name
			FROM
				Score s
			JOIN Course c ON s.c_id = c.c_id
			WHERE
				c.c_name = '数学'
			ORDER BY
				s.s_score DESC
			LIMIT 1, 2)
	UNION
			(
				SELECT
					s.s_id,
					s.s_score,
					c.c_name
				FROM
					Score s
				JOIN Course c ON s.c_id = c.c_id
				WHERE
					c.c_name = '英语'
				ORDER BY
					s.s_score DESC
				LIMIT 1, 2)
) t -- 临时表t
ON s.s_id = t.s_id
GROUP BY
  t.c_name,
  s.s_id,
  s.s_name,
  t.s_score

注意：每一个子查询，即括号中的部分，不需要将查询结果组成的表重命名，否则会报错

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

SELECT
	s.c_id,
	c.c_name,
	sum( CASE WHEN s_score > 85 AND s_score <= 100 THEN 1 ELSE 0 END ) AS '85-100',
	round( 100 * ( sum( CASE WHEN s_score > 85 AND s_score <= 100 THEN 1 ELSE 0 END ) / count(*)), 2 ) '[85,100]占比',
	sum( CASE WHEN s_score > 70 AND s_score <= 85 THEN 1 ELSE 0 END ) AS '70-85',
	round( 100 * ( sum( CASE WHEN s_score > 70 AND s_score <= 85 THEN 1 ELSE 0 END ) / count(*)), 2 ) '[70,85]占比',
	sum( CASE WHEN s_score > 60 AND s_score <= 70 THEN 1 ELSE 0 END ) AS '60-70',
	round( 100 * ( sum( CASE WHEN s_score > 60 AND s_score <= 70 THEN 1 ELSE 0 END ) / count(*)), 2 ) '[60,70]占比',
	sum( CASE WHEN s_score >= 0 AND s_score <= 60 THEN 1 ELSE 0 END ) AS '0-60',
	round( 100 * ( sum( CASE WHEN s_score > 0 AND s_score <= 60 THEN 1 ELSE 0 END ) / count(*)), 2 ) '[0,60]占比' 
FROM
	Score s
	LEFT JOIN Course c ON s.c_id = c.c_id 
GROUP BY
	s.c_id,
	c.c_name;-- 分课程统计总数和占比

注意：本题目是将case when then end函数和聚合函数一起使用。

题目30：查询学生的平均成绩及名次—比较综合，多看，定义变量，实现rank函数

自定义变量实现rank排序

select
 a.s_id  -- 学号
 ,@i:=@i+1 as '不保留空缺排名'   -- 直接i的自加，顺序一直变大，声明变量需加@
 ,@k:=(case when @avg_score=a.avg_s then @k else @i+1 end) as '保留空缺排名' -- 只有在前后二次排序值不同时才会使顺序号加1
 ,@avg_score:=avg_s as '平均分'  -- 表a中的值
from (select 
       s_id
       ,round(avg(s_score), 2) as avg_s
      from Score 
      group by s_id
      order by 2 desc)a    -- 表a：平均成绩的排序和学号
      ,(select @avg_score:=0, @i:=0, @k:=0)b   -- 表b：进行变量初始化，固定写法。

另一种实现排序的方式

select 
  t1.c_id, 
	t1.s_score-- 成绩
 ,(select count(distinct t2.s_score) -- 排名从1开始
    from Score t2
    where t2.s_score >= t1.s_score) 排名   -- 在t2分数大的情况下，统计t2的去重个数
from Score t1
group by t1.c_id, t1.s_score
order by t1.s_score desc;   -- 分数降序排列

题目31：查询各科成绩前三名的记录—比较综合，多看

采用union可以实现，即将各科分别筛选出来再进行union合并，但是当科目特别多时，会比价耗时，所以采用自连接的方式进行筛选

select
 a.s_id
 ,a.c_id
 ,a.s_score   -- a表的成绩
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score   -- 判断a的分数小于等于b的分数，要带上等号
group by 1,2,3
having count(b.s_id) <= 3   -- b中的个数至少有3个，应对分数相同的情形
order by 2, 3 desc;   -- 课程（2）的升序，成绩（3）的降序

题目32：查询每门课被选修的学生数

 select 
 c.c_id
 ,c.c_name
 ,count(s.s_id)
from Course c
join Score s
on c.c_id = s.c_id
group by 1,2;

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

 SELECT
t1.s_id
,t1.s_name
FROM
student t1
JOIN score t2 ON t1.s_id=t2.s_id
GROUP BY 1,2
HAVING COUNT(t2.s_score)=2;