![b55ddd6c62a40f6a7decbcbb24ee9ce3.png](https://i-blog.csdnimg.cn/blog_migrate/f93b02bd64c483bf09cdb29c0ac8b6ce.jpeg)
题目1:交换性别
salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句。
例如:
![9b91470367fbfb95a3928edba7c43bd5.png](https://i-blog.csdnimg.cn/blog_migrate/816cd19f5f91e6925bc661033338a1a5.png)
运行你所编写的更新语句之后,将会得到以下表:
![ab39e4ee69136cf43332acd5ae392e9b.png](https://i-blog.csdnimg.cn/blog_migrate/034ecadbf4226ccaaaeb0bd298842574.png)
【解题思路】
要想交换原表中sex列中的性别,我们可以使用case....when....对指定数据进行更换:
![a7f058a87a793329e520798690095c0a.png](https://i-blog.csdnimg.cn/blog_migrate/b4e8bb652fc792fb5cef8b40793b5861.png)
select语句只能用于查询,不能修改原表数据,因此本题需使用update语句对原表中的数据进行修改更新。其中update的用法如下:
![da3aa4396b9502dd838e365dcfb9cae7.png](https://i-blog.csdnimg.cn/blog_migrate/c12e76fd22b5173d78ad91ed65570c72.png)
【答案1】
![c61a3d10f29d5f0b2a165a9f62c8265c.png](https://i-blog.csdnimg.cn/blog_migrate/5cc146052420f67d44dc387b6439d7c7.png)
题目2:交换座位
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
注意:如果学生人数是奇数,则不需要改变最后一个同学的座位。
![bf97d14d2456a9f13475dc661742a35e.png](https://i-blog.csdnimg.cn/blog_migrate/edf29356c6b5e82ea8498f926e569ce4.png)
假如数据输入的是上表,则输出结果如下:
![7cd458236728b6c2cfd0bae966aae60a.png](https://i-blog.csdnimg.cn/blog_migrate/fd8b64712bd716da057967b7e941c1e0.png)
【解题思路】
相邻俩学生的座位一旦发生交换,则id=1变为id=2,id=2变为id=1,id=3变为id=4,id=4变为id=3,可以发现奇数位的id交换位置后变为id+1,偶数位的id交换位置后变为id-1。
因此可以使用case....when....结构来进行判断。
但如果学生总人数刚好为奇数位时,最后一个同学的座位号保持不变。
因此座位号有3种情况需要进行判断:
奇数位且为最后一位、奇数位非最后一位、偶数位
【答案2】
![33db01951c32bbe7ab3cea38ad7fc55a.png](https://i-blog.csdnimg.cn/blog_migrate/f09cba96094203f5f32f8ef8c67a7e04.png)
题目3:查找第二高的薪水
编写一个 SQL 查询,获取Employee
表中第二高的薪水(Salary) 。
![8caacfdd00fc6b9d364111720575c1f8.png](https://i-blog.csdnimg.cn/blog_migrate/17427b4e060cbc97965059a5473ef400.png)
【解题思路】
类似于这种求第N高的题目,可以采用order by排序+limit的方法来做。
本题需要注意的一点是:题目要求对查找出来的第二高薪水数据再进行判断,若存在则直接显示,若不存在则为null。因此需要对查询出来的第二高薪水使用ifnull函数判断:
ifnull(a,b)用于判断某字段或表达式是否为null。若a为null,则返回b,否则返回a
【答案3】
![501c04089a6c6aa2b7ff178a5155b8f4.png](https://i-blog.csdnimg.cn/blog_migrate/e4e1123b1512968a655fae3cc4236e40.png)
题目4:分数排名
编写一个 SQL 查询来实现分数排名。
如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
![fb228726de97713cce81a298e941f33d.png](https://i-blog.csdnimg.cn/blog_migrate/67b79710b4d03e98731f5c58f7bc37ae.png)
例如,根据上述给定的 Scores
表,你的查询应该返回(按分数从高到低排列):
![923639195191f94e89e711f159054f75.png](https://i-blog.csdnimg.cn/blog_migrate/6af8ad3e1b1e88156ed118394144ae88.png)
【解题思路】
涉及到排名问题、排名连续问题,可优先考虑窗口函数。根据题目要求,同分排名并列,且整体排名连续,因此需使用dense_rank()函数,如下:
dense_rank() over (partition by <用于分组的列名> order by <用于排序的列名>)
【答案4】
![7a29120fc06814cb75574d8abc849f1e.png](https://i-blog.csdnimg.cn/blog_migrate/8e14572cea075bc5d1d3ac976ca34d24.png)
题目5:连续出现的数字
编写一个 SQL 查询,查找所有连续出现三次的数字。示例如下:
![ab321d0e431cea125c7cfe8abcad37eb.png](https://i-blog.csdnimg.cn/blog_migrate/105dfaab2ce3b6edc63513a7c5d5ae98.png)
例如,给定上面的Logs
表,1
是唯一连续出现三次的数字。
![4fad569f6b4f76a805f8a3c21638ea09.png](https://i-blog.csdnimg.cn/blog_migrate/3e2522ee3de6873d19ac79867a9f6976.png)
【解题思路】
连续出现三次的数字,这句话中包含了两个条件:一是id连续;二是三次数字的值相同。
则利用自连接,复制出三个相同的表,并利用三表中id的特点进行连接,再利用where条件筛选出值相等的数据即可。
【答案5】
![952400856eba0c6bf833179f9191edf4.png](https://i-blog.csdnimg.cn/blog_migrate/9f7a6f915dd096a3bc7e9f5d6d8da35e.png)
题目6:查找重复的电子邮箱
编写一个 SQL 查询,查找 Person
表中所有重复的电子邮箱。示例如下:
![f7777c45720caf51c9bd664160d541ac.png](https://i-blog.csdnimg.cn/blog_migrate/cdf150e4e41fcdb114f468fff60ce338.png)
根据以上输入,你的查询应返回以下结果:
![2c3a486352f6432d08e27688dd1be8e3.png](https://i-blog.csdnimg.cn/blog_migrate/74f309239f164b3b7b9957f7628745b5.png)
【解题思路】
类似于这种查找重复数据的题目,一般先进行分组,再使用having对分组后的数据进行条件筛选。
【答案6】
![6f408e891cc1a455e26daf16567f7a10.png](https://i-blog.csdnimg.cn/blog_migrate/87053a9cac0ca5a3f6971e4494bf6d13.png)
题目7:查找从不订购的客户
某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
![5c4f12548d2d50fe505cf382192b97d8.png](https://i-blog.csdnimg.cn/blog_migrate/fa1ba0554804105ef174a79a54aa4c3a.png)
Orders
表:
![d724a223bdf30c159e6d4d9641d14bfb.png](https://i-blog.csdnimg.cn/blog_migrate/7570458b179a5b2838ea65c13f784954.png)
【解题思路】
查找从不订购的客户,其实本质上,就是找出customers表中存在,而orders表中不存在的客户,则以customers为主表,进行左连接,再进行where条件筛选即可。
【答案7】
![82e61dd54df68c5fee9d9bdc5aa531c3.png](https://i-blog.csdnimg.cn/blog_migrate/7410429c16751c9498ef45b610194613.png)
题目8:查找部门工资最高的员工
Employee
表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
编写一个 SQL 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。
解释:Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。
![756e8c8c40795aed33a6e43a651e8778.png](https://i-blog.csdnimg.cn/blog_migrate/63323b7a69eac242411b61bfcf0bdec9.png)
Department
表包含公司所有部门的信息。
![8c1b2c7dc05403e906b8ef9e88da3e0f.png](https://i-blog.csdnimg.cn/blog_migrate/9db8cbec6325510a8d4ccf9069738bd4.png)
【解题思路】
显然,需要查询的字段涉及到两个表,因此需要两表进行连接。
题目要求‘每个部门工资最高的员工’,因此可以先查询出每个部门的最高工资和部门id,再利用where筛选出来,从而找出对应的员工名字、部门以及薪水。
【答案8】
![c4b8f160d7f72ea312cb292ea570b37e.png](https://i-blog.csdnimg.cn/blog_migrate/b634ca6c72979a58aa57bac839d35b45.png)
题目9:查找部门工资前三高的所有员工
Employee
表包含所有员工信息,每个员工有其对应的工号 Id
,姓名Name
,工资Salary
和部门编号DepartmentId
。
![e37b069a75f0759f72ae114c6696ffb1.png](https://i-blog.csdnimg.cn/blog_migrate/685b473e9d78b439d81f12d3d60e0f0e.png)
Department
表包含公司所有部门的信息。
![e0007ce93366c1e714ddaaa21d6b5312.png](https://i-blog.csdnimg.cn/blog_migrate/2d7ff210b4eb9a8b324241e276ecfb2a.png)
编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:
解释:IT 部门中,Max 获得了最高的工资,Randy 和 Joe 都拿到了第二高的工资,Will 的工资排第三。销售部门(Sales)只有两名员工,Henry 的工资最高,Sam 的工资排第二。
![cb8bfe3ee1d404db3a64352d53c5da77.png](https://i-blog.csdnimg.cn/blog_migrate/0e12abd9bef779cae8fb40009e229f78.png)
【解题思路】
涉及到这种组内排名的问题,优先考虑窗口函数来解答。
注意:窗口函数中的排序使用desc降序方式,则此时排名=1则可以看成工资最高,排名=2可以看成工资第二高,以此类推。因此求工资前三高的员工,可以利用where条件筛选出来,where 排名<=3
【答案9】
![af66029a9678e11911c193c409caefa5.png](https://i-blog.csdnimg.cn/blog_migrate/5f032d6b90e9ea21c6183f42ed2807a0.png)
题目10:删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person
表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
![b403d2659dc66de609312e2bd229a576.png](https://i-blog.csdnimg.cn/blog_migrate/95ddec47b0413052249db847b9ca20f4.png)
例如,在运行你的查询语句之后,上面的Person
表应返回以下几行:
![cd0d1c8b1be6ac86813a1f9eb697e0bc.png](https://i-blog.csdnimg.cn/blog_migrate/2ea7aaa25c492bfc6f572a3dd5e14d56.png)
【解题思路】
要对原表中的数据进行删除,则必须使用delete语句。具体用法如下:
![9e62c9c4a434ac241aac8fd5c5a47187.png](https://i-blog.csdnimg.cn/blog_migrate/3612cd532ff6e4d35b95d5a17374f9c9.png)
其中where条件是:保留id最小的数据,删除其他重复的数据
我们可以先查找出id最小的数据,再利用where id not in (id最小的数据),删除其他数据即可。
【答案10】
![06e477457791605cafae6021f1071452.png](https://i-blog.csdnimg.cn/blog_migrate/3742b5d8b7c25790de0a01d92706120c.png)
题目11:上升的温度
表Weather
![98442c67d253ee7d6c57b046ba8ed9e6.png](https://i-blog.csdnimg.cn/blog_migrate/f1c223700c68769fff15c84cc79e4f68.png)
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id
。
返回结果 不要求顺序 。
查询结果格式如下例:
![8e28eefc12385a4dbda0cc57c2028a0f.png](https://i-blog.csdnimg.cn/blog_migrate/419728f6cf5513ad817f27586a344635.png)
【解题思路】
显然,本题需要使用自连接来进行判断,存在两个条件:一是日期相差为1天,二是与之前(昨天的)日期相比温度更高。
利用timestampdiff(day,a.日期,b.日期)=-1,等同于 b.日期-a.日期=-1 作为自连接的条件,再添加where筛选条件,对温度进行判断。
【答案11】
![c96b6dfb4b907f6cf6012bc750b7dda0.png](https://i-blog.csdnimg.cn/blog_migrate/9c9761b67a85fd0c315e5a048d5ccad6.png)
题目12:超过5名学生的课
有一个courses
表 ,有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
![6f176de017651188ab239d2f5df120e5.png](https://i-blog.csdnimg.cn/blog_migrate/e7749693e33a3db13bef8f05cfb1fcf9.png)
应该输出:
![2d905e6cca504034c245b383e7255765.png](https://i-blog.csdnimg.cn/blog_migrate/a504de3899f76314b4a45883a6583aff.png)
【解题思路】
对class班级进行分组,再利用having对分组后的数据进行筛选,条件即为:
count(distinct student)>=5
【答案12】
![5ea68ecd5e78c2f3b30875545170aed0.png](https://i-blog.csdnimg.cn/blog_migrate/281adc423ac27543a276c8c8bf23237f.png)
题目13:体育馆人流量
表:Stadium
![ecf3d52b40fd44e98e252ea08cc9026d.png](https://i-blog.csdnimg.cn/blog_migrate/562ef9999978b013b9121e6bfb9b7078.png)
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。查询结果格式如下所示。
![e1c20a5a8b3ad60a43e4bdd344cc7c39.png](https://i-blog.csdnimg.cn/blog_migrate/a10fcd134f65f195abaf238cfcda48c6.png)
![0130362ad892e8edd6ab67eaa70cddd7.png](https://i-blog.csdnimg.cn/blog_migrate/3037c3976b725a6a7787b7352d59dba4.png)
【解题思路】
筛选出每行都有>=100的人数记录并不难,直接使用where条件即可,但是关键点在于如何查找出连续三天。
通常情况下,题目中要求查找‘连续三天的数据’,优先考虑使用三张相同的表进行自连接。
三张表a,b,c,假设所求的字段为a.*,则需要将a的id分别设置为三个连续id中的最小值、中间值、最大值,即可得到全部的连续三个值。
【答案13】
![03102c243b4ceb26106c88bfb0def868.png](https://i-blog.csdnimg.cn/blog_migrate/53cd517b66744638b75e043abc8b1e39.png)
题目14:重新格式化部门表
编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。
部门表 Department
:
![9a8055db408e8ec6929ba3085c6b872d.png](https://i-blog.csdnimg.cn/blog_migrate/e873a20d607bd25fe066a250c5e4a461.png)
![6701d3917ab67a67918bf65ee22d82cc.png](https://i-blog.csdnimg.cn/blog_migrate/6517b956e086f03c70a77dbb52e4033e.png)
【解题思路】
对于这种行列互换问题,可采用case....when....结构来判断。
![882014b77c6f9bfb3bd7c74984be9fdd.png](https://i-blog.csdnimg.cn/blog_migrate/d0a8878cd50a753cf9d7c4517da63920.png)
先对id进行分组,当月份month=Jan时,返回对应的revenue数值,else为null,再进行求和,其他月份以此类推。
【答案14】
![aa32f7a4c53db674e6ec3905ff309509.png](https://i-blog.csdnimg.cn/blog_migrate/bb5e3e4cf63487bea980cdd47705b7d9.jpeg)
数据来源:力扣 (LeetCode) - Database
链接:
题库 - 力扣 (LeetCode)leetcode-cn.com![a891cbeb398df25915826a32bcf6d390.png](https://i-blog.csdnimg.cn/blog_migrate/5f66447bc57a217ec37e1676a97052bf.jpeg)