mysql对测试如何_MySQL：对于几个测试题的详细研究

最新推荐文章于 2025-01-20 20:22:02 发布

原创最新推荐文章于 2025-01-20 20:22:02 发布 · 104 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql对测试如何

本文深入探讨了SQL在处理复杂查询时的技巧，如联表查询、子查询优化、使用group_concat函数、case when条件判断等，并通过具体实例解析了如何求取各科成绩的平均分、及格率、成绩排名等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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;

得到最终结果：

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

查询各科成绩前三名的记录(不考虑成绩并列情况)selectscore.sid,score.student_id, score.course_id,score.score,

t1.first_score,t1.second_score,t1.third_scorefromscore inner join (select s1.sid,(select score from score as s2 where s1.course_id =s2.course_id

order by score desc limit0,1) asfirst_score,

(select score from score as s3 where s1.course_id =s3.course_id

order by score desc limit1,1) assecond_score,

(select score from score as s4 where s1.course_id =s4.course_id

order by score desc limit2,1) asthird_scorefrom score as s1) as t1 on score.sid =t1.sidwhere 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,

) AS first_score,

(

SELECT

score

FROM

score AS s3

WHERE

s1.course_id = s3.course_id

ORDER BY

score DESC

LIMIT 1,

) AS second_score,

(

SELECT

score

FROM

score AS s4

WHERE

s1.course_id = s4.course_id

ORDER BY

score DESC

LIMIT 2,

) 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和姓名；

参考思路：