(自连接、子查询、多表联查、窗口函数) MySQL经典45题重难点复习

最新推荐文章于 2023-04-27 23:51:20 发布
最新推荐文章于 2023-04-27 23:51:20 发布
阅读量347 收藏 6
点赞数 2
分类专栏： MySQL练习 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46960971/article/details/113196709
版权

MySQL经典45题中难度题型复习

经典45题涉及数据表如下
student表：
在这里插入图片描述
sc表：
在这里插入图片描述
course表：
在这里插入图片描述
teacher表:
在这里插入图片描述
注：题目难度等级因人而异，以下关于中、难的分级也是练习时根据自己的状况划分的。以下题目多涉及子链接、自连接、多表连接等重难点，值得反复思考

自连接

-- （中）1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT*FROM sc a LEFT JOIN student b on a.sid=b.sid
INNER JOIN sc c ON a.sid=c.sid AND a.cid=01 AND c.cid=02
WHERE a.score > c.score
-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT * FROM sc a INNER JOIN sc b ON a.sid=b.sid
WHERE a.cid=01 AND b.cid=02
-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null) 
SELECT*FROM sc a LEFT JOIN sc b ON a.sid=b.sid AND b.cid=02
WHERE a.cid=01 
-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM (SELECT * FROM sc WHERE sid 
NOT IN (SELECT sid FROM sc WHERE cid=01)) a
INNER JOIN sc b
on a.sid=b.sid AND b.cid=02

子查询与多表联查

-- （中）7.查询没有学全所有课程的同学的信息
SELECT b.*,COUNT(CId) FROM sc a 
INNER JOIN student b ON a.sid=b.SId
group by sid
HAVING count(cid) < (SELECT count(cid) FROM course)

-- (中)39.查询选修了全部课程的学生信息
SELECT b.*,count(cid) FROM sc a
INNER JOIN student b on a.sid=b.sid
GROUP BY sid 
HAVING count(cid)=(SELECT count(cid) FROM course) 

-- （中）8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT b.* FROM sc a INNER JOIN student b on a.sid=b.sid
where a.cid IN (SELECT cid FROM sc WHERE sid=01)
GROUP BY a.sid

-- （难）9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT c.* FROM student c 
       INNER JOIN 
            (SELECT sid,GROUP_CONCAT(CId order by CId asc) as courses 
            FROM sc GROUP BY SId) a
       INNER JOIN
            (SELECT sid,GROUP_CONCAT(CId order by CId asc) as courses
             FROM sc GROUP BY SId HAVING sid = 01) b
      ON  a.sid!=01 AND a.courses=b.courses AND a.sid=c.sid

-- （难）10.查询没学过“张三”老师讲授的任一门课程的学生姓名
SELECT *
FROM student where sid NOT IN
(SELECT sid FROM sc c 
LEFT JOIN course b on b.cid=c.cid
INNER JOIN teacher a on a.tid=b.tid where a.tname='张三')

-- (难)11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT b.sid,b.sname,avg(score) FROM sc a 
LEFT JOIN student b ON a.sid=b.sid 
INNER JOIN 
(SELECT sid FROM sc WHERE score<60 GROUP BY sid HAVING count(cid) >=2)c 
on a.sid=c.sid
GROUP BY b.sid

CASE WHEN

-- (中)14.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT a.cid,b.cname,MAX(a.score) AS '最高分',MIN(a.score) AS '最低分',AVG(a.score) AS '平均分',COUNT(sid) AS '选课人数',
sum(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(sid) AS '及格率',
sum(CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END)/COUNT(sid) AS '中等率',
sum(CASE WHEN score>=80 AND score<90 THEN 1 ELSE 0 END)/COUNT(sid)AS '优良率',
sum(CASE WHEN score>=90 AND score<=100 THEN 1 ELSE 0 END)/COUNT(sid) AS '优秀率'
FROM sc a 
INNER JOIN course b
ON a.cid=b.cid
GROUP BY a.cid
ORDER BY '选课人数' DESC,a.cid ASC

--(中)17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT a.cid,b.cname,
concat(SUM(CASE WHEN score>=85 and score<=100 THEN 1 ELSE 0 END )/ count(sid)*100,'%')AS '100-85',
concat(SUM(CASE WHEN score>=70 and score<85 THEN 1 ELSE 0 END)/ count(sid)*100,'%')AS '70-85',
concat(SUM(CASE WHEN score>=60 and score<70 THEN 1 ELSE 0 END)/ count(sid)*100,'%')AS '70-60',
concat(SUM(CASE WHEN score>=0 and score<70 THEN 1 ELSE 0 END)/ count(sid)*100,'%')AS '60-0'
FROM sc a INNER JOIN course b
ON a.cid=b.cid
GROUP BY cid

开窗函数

-- (中)13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT c.sid,c.cid,e.cname,c.score,d.avg_sco FROM
(SELECT a.sid,a.cid,a.score FROM sc a INNER JOIN student b on a.sid=b.sid) c
LEFT JOIN 
(SELECT a.sid,AVG(score) as avg_sco FROM sc a  GROUP BY a.sid) d
on c.sid=d.sid
LEFT JOIN course e on c.cid=e.cid
ORDER BY avg_sco DESC
  
-- 开窗函数解法
SELECT a.sid,b.cid,b.score,avg(b.score) over ( PARTITION BY b.sid ) AS avg_sco 
FROM student a INNER JOIN sc b
ON a.sid = b.sid ORDER BY avg_sco DESC

-- (难)15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺 
SELECT sid,cid,score,@rank:=@rank+1 FROM sc ,(select @rank:=0) r
ORDER BY score DESC
-- 开窗函数解法
select a.*, 
rank() over(partition by a.cid order by a.score desc) as rn
from sc a;


-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
SELECT sid,cid,score,CASE WHEN score=@sco THEN @rank
WHEN @sco:=score THEN @rank:=@rank+1 end as rn
FROM sc,(SELECT @rank:=0,@sco:=null)as r
ORDER BY score DESC
-- 开窗函数解法
select cid,score,dense_rank() over (partition by cid order by score desc)  as "rank" 
from sc;

-- (难)16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺 
SELECT s.*,@rank:=IF(@sum_sco=sum_sco,'',@rank+1) as r,@sum_sco:=sum_sco FROM 
(SELECT sid,sum(score) AS sum_sco FROM sc 
GROUP BY sid
ORDER BY sum_sco DESC) as s,(SELECT @rank:=0,@sum_sco:=null ) as t
--窗口函数解法
select *, dense_rank() over (order by sum_score desc) as rank1
from (select sc.sid, sum(sc.score) as sum_score from sc group by sc.sid) a;

-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT s.*,@rank:=IF(@sum_sco=sum_sco,@rank,@rank+1) as r,@sum_sco:=sum_sco FROM 
(SELECT sid,sum(score) AS sum_sco FROM sc 
GROUP BY sid
ORDER BY sum_sco DESC) as s,(SELECT @rank:=0,@sum_sco:=null ) as t
--窗口函数解法
select *, rank() over (order by sum_score desc) as rank1 from 
(select sc.sid, sum(sc.score) as sum_score from sc group by sc.sid)a;

-- (中)18.查询各科成绩前三名的记录
     # 前三名转化为大于此成绩的数量少于3，构造笛卡尔积
SELECT a.* FROM sc a 
WHERE(select count(1) FROM sc b WHERE b.cid=a.cid and b.score>a.score)<3
ORDER BY cid asc,score desc
--窗口函数解法
select * from 
(select*,dense_rank()over(partition by cid order by score desc) as r from sc) a
where r<=3

-- (中)36.查询每门功成绩最好的前两名
SELECT a.*FROM sc a where 
(select count(sid) FROM sc b where a.cid=b.cid and b.score>a.score)<2
ORDER BY cid asc,score desc

-- (难)34.假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT * FROM (SELECT a.*,CASE WHEN @score=score THEN @rank
                WHEN @score:=score THEN @rank:=@rank+1 END as rn FROM
(SELECT a.sid,b.sname,c.cid,c.cname,d.tname,a.score 
FROM sc a,student b,course c,teacher d 
WHERE a.sid=b.sid AND a.cid=c.cid AND c.tid=d.tid AND d.tname='张三' ) a,
(SELECT @rank:=0,@score:=null)t)s
where rn=1

--开窗函数解法
select t.* from 
(select a.sid,d.sname,b.cname,c.tname,a.score,rank()over(order by a.score desc) as r 
from sc a join course b on a.cid=b.cid
join teacher c on b.tid=c.tid
join student d on a.sid=d.sid where c.tname = '张三')t
where r=1

日期函数

-- (中)42.查询本周过生日的学生
SELECT *, SUBSTR(YEARWEEK(sage),5,2), SUBSTR(YEARWEEK(CURDATE()),5,2)
FROM student 
WHERE SUBSTR(YEARWEEK(sage),5,2)=SUBSTR(YEARWEEK(CURDATE()),5,2)

-- (中)43.查询下周过生日的学生
SELECT *, SUBSTR(YEARWEEK(sage),5,2), SUBSTR(YEARWEEK(CURDATE()),5,2)
FROM student 
WHERE SUBSTR(YEARWEEK(sage),5,2)=SUBSTR(YEARWEEK(CURDATE()),5,2)+1

-- (中)44.查询本月过生日的学生
SELECT *, month(sage), month(CURDATE())
FROM student 
WHERE  month(sage)= month(CURDATE())

-- (中)45.查询下月过生日的学生
SELECT *, month(sage), month(CURDATE())
FROM student 
WHERE  month(sage)= month(CURDATE())+1
O_Oreo
关注
专栏目录
MySql重点复习——多表连接查询
Hydra的博客
10-08 802
连接查询 连接查询主要分为三种：内连接、外连接、交叉连接 交叉连接 交叉连接即笛卡儿乘积，是指两个关系中所有元组的任意组合。一般情况下，交叉查询是没有实际意义的。 例如：如果希望得到学生表和选课表两个关系模式的乘积，查询语句为 SELECT* FROM学生表CROSS JOIN选课表 内连接 内连接是一种最常用的连接类型。内连接查询实际上是一种任意条件的查询。使用内连接时，如...
MySql的复习(五)：联表查询和子查询
BKSW.的博客
02-27 1035
MySql的复习(五)：联表查询和子查询 查询结果去重 关于查询结果的去重：distinct关键字 select distinct job from emp; // distinct关键字去除重复记录。 distinct关键字只能用于所有字段的前面。 错误写法：select ename,distinct job from emp; 正确写法：select distinct deptno,job from emp; 关于表的别名 关于表的别名： select e.ename,d.dname from emp
Mysql自连接查询实例详解
09-09
主要介绍了Mysql自连接查询,结合实例形式分析了MySQL自连接查询的应用场景、原理及相关操作技巧,需要的朋友可以参考下
mysql比较难的练习_mysql练习题
weixin_33260887的博客
02-08 236
一、表关系请创建如下表，并创建相关约束二、操作表1、自行创建测试数据2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；3、查询平均成绩大于60分的同学的学号和平均成绩；4、查询所有同学的学号、姓名、选课数、总成绩；5、查询姓“李”的老师的个数；6、查询没学过“叶平”老师课的同学的学号、姓名；7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；8、查询学过“叶平”老师所教的...
MySQL--经典题目综合汇总二（进阶）--建议先把之前的看了，难度较高
L1542334210的博客
10-08 524
MySQL--经典题目综合汇总二（进阶）--建议先把之前的看了，难度较高1、表格创建2、题目部分题目一：求所有课程平均成绩排名在2到4名的同学信息(压轴)题目二：查询不同老师所教不同课程平均分从高到低显示(压轴)题目三：查询出只有两门课程的全部学生的学号和姓名题目四：查询没有学全所有课程的同学的信息题目五：查询没学过“汪院长”老师授课的同学的信息题目六：查询至少有两门课与学号为“002”的同学所学...
mysql连接查询实例_Mysql自连接查询实例详解
weixin_39760650的博客
02-08 117
本文实例讲述了Mysql自连接查询。分享给大家供大家参考，具体如下：自连接查询假想以下场景：某一电商网站想要对站内产品做层级分类，一个类别下面有若干子类，子类下面也会有别的子类。例如数码产品这个类别下面有笔记本，台式机，智能手机等；笔记本，台式机，智能手机又可以按照品牌分类；品牌又可以按照价格分类，等等。也许这些分类会达到一个很深的层次，呈现一种树状的结构。那么这些数据要怎么在...
自连接概念和练习
Leon_Jinhai_Sun的博客
10-19 352
select * from emp; ---查询出员工姓名，员工领导姓名 ---自连接：自连接其实就是站在不同的角度把一张表看成多张表。 select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; ------查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称 select e1.ename, d1.dn...
mysql面试题sql语句多表联查_MySQL多表查询优化
weixin_35705450的博客
02-08 2310
一、多表查询连接的选择： 相信这内连接，左连接什么的大家都比较熟悉了，当然还有左外连接什么的，基本用不上我就不贴出来了。这图只是让大家回忆一下，各种连接查询。 然后要告诉大家的是，需要根据查询的情况，想好使用哪种连接方式效率更高。二、MySQL的JOIN实现原理在MySQL 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，他没有其他很多数据库所提供的Hash Join，...
数据库基础-12-多表查询（内连接、外连接、自连接、联合查询、子查询）
最新发布
记录java学习日常~
04-27 3890
指的是两个表之间的一种特殊连接方式，即将一个表的每条记录与另一个表的每条记录进行组合，得到的所有组合构成的新表。新表的行数为第一个表的行数乘以第二个表的行数，因此，当两个表的数据量较大时，笛卡尔积会产生非常多的组合结果，从而影响查询效率和性能。(在多表查询时，需要消除无效的笛卡尔积)。上面的 SQL 语句将会从 student 表中取得所有的学生记录，同时从 course 表中取得所有的课程记录，然后将这两个结果集进行组合，生成一个新表，其中包含每位学生选修的所有课程的组合结果。
mysql多表查询分页查询_SQLServer多表联查和多表分页查询的方法（代码示例）
weixin_36337389的博客
01-18 1502
本篇文章给大家带来的内容是关于SQLServer多表联查和多表分页查询的方法(代码示例)，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。多表联查:select p.*,s.Sheng , i.Shifrom [dbo].[ProductRecordInfo] --表名p left join [ShengInfo] s on p.ShengInfo = s.ShengId ...
MYSQL语句复杂查询大全
10-02
自己总结的Mysql语句，做了一个大项目，把所有的语句全部整合起来了，分享出来
mysql 4表关联查询_mysql之新手4表联查
weixin_28695903的博客
01-19 2916
需求：查询平台用户邀请人的投资记录思路：先是多表联查出结果，然后优化SQLtb_fund_user 用户表，tb_p2p_orderitem 订单详情 (订单详情是用户表，订单表，产品表的关联关系表)tb_p2p_order 订单表tb_p2p_product 产品表SQL语句：SELECT r.`name` AS 用户名,r.`tel` AS 手机号码,o.`order_amount` AS ...
mysql三表联查sql语句_SQL三表连接查询与集合的并、交、差运算查询
weixin_30110543的博客
02-07 7566
usedb_sqlserver2select姓名,工资,面积,金额,(工资+金额/1000)as实发工资from职工,仓库,订购单where职工.职工号=订购单.职工号and职工.仓库号=仓库.仓库号2：select姓名,工资,金额from职工,订购单where姓名like'%平%'and职工.职工号=订购单.职工号orderby工资des...
MySQL趣味题_关于子查询和连接的趣味小题目
weixin_42303282的博客
02-03 112
今天有位同学出来一个趣味小题。 A： select title from simplified where id in (select id from analysis where word = something); B： select b.title from analysis a join simplified b on (a.id=b.id) where a.word=something;...
mysql每日一题-窗口函数练习0916
onemorepoint的博客
09-16 195
solution 可以使用窗口函数进行筛选出按照日期每个日期出现的次数，然后筛选出来两个数值相等的记录来，当然可以通过join的方式进行查询 select workid,recdate, rectime, time4 from (select *, count(1) over(PARTITION by recdate ) num from T0916 ) a where a.num&...
数据库连接与子查询习题
雄性皮卡丘
02-17 723
--每一个部门的平均薪水-- select deptno, avg(sal) from emp group by deptno --取得平均薪水大于2000的部门-- select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000 --薪水大于1200的雇员按照部门编号分组，分组
Mysql8.0习题系列（八）：窗口函数（一篇学会rank、dense_rank、row_number使用，超详细~）
阿里云专家博主、数据科学领域优质创作者、统计er在读
03-31 1553
本文给出了mysql8.0新支持的窗口函数之排序函数习题，主要区分了rank()、dense_rank()、row_number()三者之间的区别。
21、自连接及联表查询练习
qq_23889885的博客
11-21 131
自连接/窗口函数/having和where教程-180 连续出现的数字 182 重复电子邮箱
weixin_40112434的博客
05-04 977
先来一个教程,having和where的区别到底在哪[面试被问到了] SQL中having和where的区别
MySQL高级查询与多表联查详解及示例
高级查询通常涉及更复杂的查询语句，如子查询、连接查询（JOIN）、聚合函数（如SUM, COUNT, AVG等）以及窗口函数等。这些查询可以帮助我们从大量数据中提取所需的信息，并且可以进行数据筛选、排序和分组。例如，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值