Fork me on GitHub

MySQL:对于几个测试题的详细研究

  最近在做MySQL作业时候遇到了很多问题,MySQL作业链接:https://www.cnblogs.com/wj-1314/p/9213885.html

所以下面谈一下稍微难一点的数据库问题,我们需要如何解决,这里一步一步的分解题目,得到答案。可能有些题目确实超出我的能力范围,希望看到的盆友能帮一下,谢谢。

  首先这里总结一下几个重点。

1,上述作业要求使用SQL,不能使用视图。

2,一般来说,联表查询的效率要高于子查询,建议多使用联表查询。

 

一,按各科平均成绩从低到高和及格率的百分数从高到低顺序;

  这道题的难点就是及格率的问题,如果只是单纯的按照各科平均成绩从低到高排序,那么很简答,如下:

select course_id,avg(score) as avg_score from score group by course_id order by avg_score asc;

  结果如下:

  那么及格率的百分数如何做呢,我们分析一下题目,首先是及格率,其次是百分数,

  对于及格率的问题,我们的思路是  case when....then.....  简单的说一下这个,相当于三木运算,

     select sum(case when score.score >60 then 1 else 0 end)/count(1) *100 
from score group by course_id;

  结果:

所以联立两个即可。

select course_id,avg(score) as avg_score, 
	 sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent
	 from score group by course_id order by avg(score) asc,percent desc;

 二,课程平均分从高到低显示(显示任课老师)

    首先在score中查找平均分数,并按照课程id分组,程序如下:

select course_id,avg(score) from score group by course_id;

  结果:

 把分数由低到高,则如下:

select course_id,avg(score) from score group by course_id order by avg(score) desc;

  结果:

  最后显示任课老师,这就需要连立表格了,也就是联表操作,从表格数据我们发现,以课程表为中心,联力老师,成绩表格即可,如下:

select course_id,teacher.tname,avg(score) from course
 left join score on course.cid = score.course_id 
 left join teacher on course.teacher_id = teacher.tid
  group by course_id order by avg(score) desc;

  得到最终结果:

 

三,查询各科成绩前三名的记录(不考虑成绩并列的情况)

查询各科成绩前三名的记录(不考虑成绩并列情况)
    
    select score.sid,score.student_id, score.course_id,score.score,
     t1.first_score,t1.second_score,t1.third_score
     from score inner join (
     select s1.sid,(select score from score as s2 where s1.course_id = s2.course_id 
      order by score desc limit 0,1) as first_score,
     (select score from score as s3 where s1.course_id = s3.course_id 
      order by score desc limit 1,1) as second_score,
     (select score from score as s4 where s1.course_id = s4.course_id 
      order by score desc limit 2,1) as third_score
    from score as s1) as t1 on score.sid = t1.sid
    where score.score in (t1.first_score,t1.second_score,t1.third_score);

   上面的答案是我抄的别人的,我承认我初次接触,了解不是很深,今天就特意反思了一下,深入研究。

  首先分析一下题目,这句话是什么意思? 是按人分类,每个人的各科成绩中位于自己科目成绩的前三;还是按科目分类,每科成绩前三名的人?

  从下面的结果来看,是有点杂糅,我自己也有点懵逼。

   但是仔细分析,确实没错,我把SQL美化一下,如下:

SELECT
	score.sid,
	score.student_id,
	score.course_id,
	score.score,
	t1.first_score,
	t1.second_score,
	t1.third_score
FROM
	score
INNER JOIN (
	SELECT
		s1.sid,
		(
			SELECT
				score
			FROM
				score AS s2
			WHERE
				s1.course_id = s2.course_id
			ORDER BY
				score DESC
			LIMIT 0,
			1
		) AS first_score,
		(
			SELECT
				score
			FROM
				score AS s3
			WHERE
				s1.course_id = s3.course_id
			ORDER BY
				score DESC
			LIMIT 1,
			1
		) AS second_score,
		(
			SELECT
				score
			FROM
				score AS s4
			WHERE
				s1.course_id = s4.course_id
			ORDER BY
				score DESC
			LIMIT 2,
			1
		) AS third_score
	FROM
		score AS s1
) AS t1 ON score.sid = t1.sid
WHERE
	score.score IN (
		t1.first_score,
		t1.second_score,
		t1.third_score
	);

  这下来分析,就是每一科成绩都有前三名,难点就是取每一科目的前三,这点我不是很熟悉。

四,group_concat的具体用法及其实例

  group_concat函数的具体语法如下:

group_concat( [DISTINCT]  要连接的字段   [Order BY 排序字段 ASC/DESC]   [Separator '分隔符'] )

  group_concat的基本功能是:以course_id分组,把sid字段的值在同一行打印出来,逗号分隔(默认)

如下:

 select group_concat(sid) from score group by course_id;

  结果:

 五,case when....then的用法

 case具有两种格式,简单的case函数和case搜索函数。

简单的case函数:

CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

case搜索函数:

CASE WHEN sex = '1' THEN '男' 
WHEN sex = '2' THEN '女' 
ELSE '其他' END  

  2种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。

练习:查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级)

示例结果如下:

             班级id    班级名称         年级    年级级别
                   1   一年一班        一年级       低

 此题难点就是设置年级级别,如果忽略了这个,则查询班级信息,非常简单。如下:

select * from class;

  结果:

     根据上面的语法,我们使用case搜索函数,写的年级级别如下:

case 
	       when class_grade.gid between 1 and 2 then '低'
		when class_grade.gid between 3 and 4 then '中'
		when class_grade.gid between 5 and 6 then '高' 
                else 0 end as '年级级别'

  写出来后,组合即可。

	select
		class.cid as '班级id',
		class.caption as '班级名称',
		class_grade.gname as '年级',
	case 
	        when class_grade.gid between 1 and 2 then '低'
		when class_grade.gid between 3 and 4 then '中'
		when class_grade.gid between 5 and 6 then '高' 
                else 0 end as '年级级别'
        from  class
	left join class_grade on class.grade_id = class_grade.gid;    

六,MySQL中燃控函数如何实现?

  MySQL中不存在类似于SQL Server或Orcal中的rank()函数来得到排名。而在实际的工作中,常常需要将查询后排序得到的排名给记录下来。由于项目需要,不仅要对成绩进行排名,而且需要相同成绩的具有相同的排名。根据网上的提供的排名方法,进一步进行扩充,得到了下面的实现方式。

链接:https://www.jianshu.com/p/bb1b72a1623e

题目:查询每门课程成绩最好的前两名学生id和姓名;

参考思路:

 

posted @ 2018-06-25 14:18  战争热诚  阅读(736)  评论(0编辑  收藏  举报