百度360必应搜狗淘宝本站头条
当前位置:网站首页 > IT技术 > 正文

10道有深度的mysql查询语句面试题

wptr33 2024-11-09 15:00 50 浏览

介绍

很多查询都可以用经典的学生-课程-成绩案例来表示,下面是一些我在笔试或面试中遇到的经典题型。这些题目没有涉及到索引,函数,存储过程等高级功能,都是一些基本的查询语句,但难度却不小。

表结构

  • 学生表student(id,name)
  • 列表项目
  • 课程表course(id,name)
  • 学生课程表student_course(sid,cid,score)
  • sql
create table student(
id int unsigned primary key auto_increment,
name char(10) not null
);
insert into student(name) values('张三'),('李四');

create table course(
id int unsigned primary key auto_increment,
name char(20) not null
);
insert into course(name) values('语文'),('数学');

create table student_course(
sid int unsigned,
cid int unsigned,
score int unsigned not null,
foreign key (sid) references student(id),
foreign key (cid) references course(id),
primary key(sid, cid)
);

insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);

问题

  • 查询student表中重名的学生,结果包含id和name,按name,id升序
select id,name
from student
where name in (
select name from student group by name having(count(*) > 1)
) order by name;

我们经常需要查询某一列重复的行,一般通过group by(有重复的列)然后取count>1的值。 关系型数据库有他的局限性, 有些看似简单的查询写出来的sql很复杂,而且效率也会很低。

  • 在student_course表中查询平均分不及格的学生,列出学生id和平均分
select sid,avg(score) as avg_score
from student_course
group by sid having(avg_score<60);

group by和having是最常考的。 where子句中不能用聚集函数作为条件表达式,但是having短语可以,where和having的区别在于对用对象不同,where作用于记录,having作用于组。

  • 在student_course表中查询每门课成绩都不低于80的学生id
select distinct sid
from student_course
where sid not in (select sid from student_course where score < 80);

用到反向思想,其实就是数理逻辑中的?x:P和??x:?P是等价的。

  • 查询每个学生的总成绩,结果列出学生姓名和总成绩 如果使用下面的sql会过滤掉没有成绩的人
select name,sum(score) total
from student,student_course
where student.id=student_course.sid
group by sid;

更保险的做法应该是使用 左外连接

select name,sum(score)
from student left join student_course
on student.id=student_course.sid
group by sid;
  • 总成绩最高的学生,结果列出学生id和总成绩 下面的sql效率很低,因为要重复计算所有的总成绩。
select sid,sum(score) as sum_score
from student_course 
group by sid 
having sum_score>=all(
 select sum(score) from student_course group by sid
 );

因为order by中可以使用聚集函数,最简单的方法是:

select sid,sum(score) as sum_score
from student_course 
group by sid
order by sum_score desc 
limit 1;

同理可以查总成绩的前三名。

  • 在student_course表查询课程1成绩第2高的学生,如果第2高的不止一个则列出所有的学生

这是个查询 第N大数 的问题。 我们先查出第2高的成绩:

select min(score) from student_course 
where cid = 1 
group by score
order by score desc 
limit 2;

使用这种方式是错的,因为作用的先后顺序是group by->min->order by->limit,mysql提供了limit offset,size这种方式来取第N大的值,因此正确的做法是:

select score from student_course 
where cid = 1 
group by score 
order by score desc limit 1,1;

然后再取出该成绩对应的学生:

select * from student_course
where cid=1 and score = (
 select score from student_course 
 where cid = 1 
 group by score 
 order by score desc limit 1,1
);

类似的,可以查询 某个值第N高 的记录。

  • 在student_course表查询各科成绩最高的学生,结果列出学生id、课程id和对应的成绩 你可能会这样写:
select sid,cid,max(score) from student_course group by cid;

然而上面是不对的,因为 使用了group by的查询字段只能是group by中的字段或者聚集函数或者是每个分组内均相同的字段。 虽然不会报错,但是sid是无效的,如果去掉sid的话只能查出没门课程的最高分,不包含学生id。 本题的正确解法是使用相关嵌套查询:

select * from student_course as x 
where score>=(
 select max(score) from student_course as y 
 where cid=x.cid
 );

相关嵌套查询也就是在进行内层查询的时候需要用到外层查询,有一些注意事项:

  1. 子查询一定要有括号
  2. as可以省略
  3. 使用相关查询;>=max等价于>=all,但是聚合函数比使用any或all效率高
  • 在student_course表中查询每门课的前2名,结果按课程id升序,同一课程按成绩降序 这个问题也就是取每组的前N条纪录
 select * from student_course x 
 where 2>(
 select count(*) from student_course y 
 where y.cid=x.cid and y.score>x.score
 ) 
order by cid,score desc;

这也是一个相关嵌套查询,对于每一个分数,如果同一门课程下只有0个、1个分数比这个分数还高,那么这个分数肯定是前2名之一

  • 一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球队,两两进行比赛,用一条sql语句显示所有可能的比赛组合
  • select a.name, b.name
  • from team a, team b
  • where a.name < b.name

其实就是一个表和自己连接查询。

  • 题目:数据库中有一张如下所示的表,表名为sales。

年 季度 销售

1991 1 11

1991 2 12

1991 3 13

1991 4 14

1992 1 21

1992 2 22

1992 3 23

1992 4 24

要求:写一个SQL语句查询出如下所示的结果。

年 一季度 二季度 三季度 四季度

1991 11 12 13 14

1992 21 22 23 24

select 年, 
sum(case when 季度=1 then 销售量 else 0 end) as 一季度, 
sum(case when 季度=2 then 销售量 else 0 end) as 二季度, 
sum(case when 季度=3 then 销售量 else 0 end) as 三季度, 
sum(case when 季度=4 then 销售量 else 0 end) as 四季度 
from sales group by 年;

同理,如果要查询每个人的每门课的成绩可以使用如下sql

create view temp 
as 
select student.name as sname,course.name as cname,score
from student_course join (student,course)
on(student_course.sid=student.id and student_course.cid=course.id);
select sname,
sum(case when cname='语文' then score else 0 end) as 语文,
sum(case when cname='数学' then score else 0 end) as 数学
from temp
group by sname;

当然如果新增了一门课,第二条sql就需要跟着变。

推荐阅读:

SQL JOIN,你想知道的应该都有

原文:https://segmentfault.com/a/1190000015570878

相关推荐

[常用工具] git基础学习笔记_git工具有哪些

添加推送信息,-m=messagegitcommit-m“添加注释”查看状态...

centos7安装部署gitlab_centos7安装git服务器

一、Gitlab介1.1gitlab信息GitLab是利用RubyonRails一个开源的版本管理系统,实现一个自托管的Git项目仓库,可通过Web界面进行访问公开的或者私人项目。...

太高效了!玩了这么久的Linux,居然不知道这7个终端快捷键

作为Linux用户,大家肯定在Linux终端下敲过无数的命令。有的命令很短,比如:ls、cd、pwd之类,这种命令大家毫无压力。但是,有些命令就比较长了,比如:...

提高开发速度还能保证质量的10个小窍门

养成坏习惯真是分分钟的事儿,而养成好习惯却很难。我发现,把那些对我有用的习惯写下来,能让我坚持住已经花心思养成的好习惯。...

版本管理最好用的工具,你懂多少?

版本控制(Revisioncontrol)是一种在开发的过程中用于管理我们对文件、目录或工程等内容的修改历史,方便查看更改历史记录,备份以便恢复以前的版本的软件工程技术。...

Git回退到某个版本_git回退到某个版本详细步骤

在开发过程,有时会遇到合并代码或者合并主分支代码导致自己分支代码冲突等问题,这时我们需要回退到某个commit_id版本1,查看所有历史版本,获取git的某个历史版本id...

Kubernetes + Jenkins + Harbor 全景实战手册

Kubernetes+Jenkins+Harbor全景实战手册在现代企业级DevOps体系中,Kubernetes(K8s)、Jenkins和Harbor组成的CI/CD流水...

git常用命令整理_git常见命令

一、Git仓库完整迁移完整迁移,就是指,不仅将所有代码移植到新的仓库,而且要保留所有的commit记录1.随便找个文件夹,从原地址克隆一份裸版本库...

第三章:Git分支管理(多人协作基础)

3.1分支基本概念分支是Git最强大的功能之一,它允许你在主线之外创建独立的开发线路,互不干扰。理解分支的工作原理是掌握Git的关键。核心概念:HEAD:指向当前分支的指针...

云效Codeup怎么创建分支并进行分支管理

云效Codeup怎么创建分支并进行分支管理,分支是为了将修改记录分叉备份保存,不受其他分支的影响,所以在同一个代码库里可以同时进行多个修改。创建仓库时,会自动创建Master分支作为默认分支,后续...

git 如何删除本地和远程分支?_git怎么删除远程仓库

Git分支对于开发人员来说是一项强大的功能,但要维护干净的存储库,就需要知道如何删除过时的分支。本指南涵盖了您需要了解的有关本地和远程删除Git分支的所有信息。了解Git分支...

git 实现一份代码push到两个git地址上

一直以来想把自己的博客代码托管到github和coding上想一次更改一次push两个地址一起更新今天有空查资料实践了下本博客的github地址coding的git地址如果是Gi...

git操作:cherry-pick和rebase_git cherry-pick bad object

在编码中经常涉及到分支之间的代码同步问题,那就需要cherry-pick和rebase命令问题:如何将某个分支的多个commit合并到另一个分支,并在另一个分支只保留一个commit记录解答:假设有两...

模型文件硬塞进 Git,GitHub 直接打回原形:使用Git-LFS管理大文件

前言最近接手了一个计算机视觉项目代码是屎山就不说了,反正我也不看代码主要就是构建一下docker镜像,测试一下部署的兼容性这本来不难但是,国内服务器的网络环境实在是恶劣,需要配置各种镜像(dock...

防弹少年团田柾国《Euphoria》2周年 获世界实时趋势榜1位 恭喜呀

当天韩国时间凌晨3时左右,该曲在Twitter上以“2YearsWithEuphoria”的HashTag登上了世界趋势1位。在韩国推特实时趋势中,从上午开始到现在“Euphoria2岁”的Has...