sql update 按条件更新_SQL常见练习题【数据分析面试必备】

题目1：交换性别

salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

运行你所编写的更新语句之后，将会得到以下表:

【解题思路】

要想交换原表中sex列中的性别，我们可以使用case....when....对指定数据进行更换：

select语句只能用于查询，不能修改原表数据，因此本题需使用update语句对原表中的数据进行修改更新。其中update的用法如下：

【答案1】

题目2：交换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。

假如数据输入的是上表，则输出结果如下：

【解题思路】

相邻俩学生的座位一旦发生交换，则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】

题目3：查找第二高的薪水

编写一个 SQL 查询，获取Employee 表中第二高的薪水（Salary） 。

【解题思路】

类似于这种求第N高的题目，可以采用order by排序+limit的方法来做。

本题需要注意的一点是：题目要求对查找出来的第二高薪水数据再进行判断，若存在则直接显示，若不存在则为null。因此需要对查询出来的第二高薪水使用ifnull函数判断：

ifnull（a,b）用于判断某字段或表达式是否为null。若a为null，则返回b，否则返回a

【答案3】

题目4：分数排名

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores表，你的查询应该返回（按分数从高到低排列）：

【解题思路】

涉及到排名问题、排名连续问题，可优先考虑窗口函数。根据题目要求，同分排名并列，且整体排名连续，因此需使用dense_rank()函数，如下：

dense_rank() over (partition by <用于分组的列名> order by <用于排序的列名>)

【答案4】

题目5：连续出现的数字

编写一个 SQL 查询，查找所有连续出现三次的数字。示例如下：

例如，给定上面的Logs表，1是唯一连续出现三次的数字。

【解题思路】

连续出现三次的数字，这句话中包含了两个条件：一是id连续；二是三次数字的值相同。

则利用自连接，复制出三个相同的表，并利用三表中id的特点进行连接，再利用where条件筛选出值相等的数据即可。

【答案5】

题目6：查找重复的电子邮箱

编写一个 SQL 查询，查找 Person表中所有重复的电子邮箱。示例如下：

根据以上输入，你的查询应返回以下结果：

【解题思路】

类似于这种查找重复数据的题目，一般先进行分组，再使用having对分组后的数据进行条件筛选。

【答案6】

题目7：查找从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

Orders表：

【解题思路】

查找从不订购的客户，其实本质上，就是找出customers表中存在，而orders表中不存在的客户，则以customers为主表，进行左连接，再进行where条件筛选即可。

【答案7】

题目8：查找部门工资最高的员工

Employee表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

Department 表包含公司所有部门的信息。

【解题思路】

显然，需要查询的字段涉及到两个表，因此需要两表进行连接。

题目要求‘每个部门工资最高的员工’，因此可以先查询出每个部门的最高工资和部门id，再利用where筛选出来，从而找出对应的员工名字、部门以及薪水。

【答案8】

题目9：查找部门工资前三高的所有员工

Employee表包含所有员工信息，每个员工有其对应的工号 Id，姓名Name，工资Salary和部门编号DepartmentId。

Department表包含公司所有部门的信息。

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

解释：IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

【解题思路】

涉及到这种组内排名的问题，优先考虑窗口函数来解答。

注意：窗口函数中的排序使用desc降序方式，则此时排名=1则可以看成工资最高，排名=2可以看成工资第二高，以此类推。因此求工资前三高的员工，可以利用where条件筛选出来，where 排名<=3

【答案9】

题目10：删除重复的电子邮箱

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

例如，在运行你的查询语句之后，上面的Person表应返回以下几行:

【解题思路】

要对原表中的数据进行删除，则必须使用delete语句。具体用法如下：

其中where条件是：保留id最小的数据，删除其他重复的数据

我们可以先查找出id最小的数据，再利用where id not in （id最小的数据），删除其他数据即可。

【答案10】

题目11：上升的温度

表Weather

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 不要求顺序 。

查询结果格式如下例：

【解题思路】

显然，本题需要使用自连接来进行判断，存在两个条件：一是日期相差为1天，二是与之前（昨天的）日期相比温度更高。

利用timestampdiff（day,a.日期,b.日期）=-1，等同于 b.日期-a.日期=-1 作为自连接的条件，再添加where筛选条件，对温度进行判断。

【答案11】

题目12：超过5名学生的课

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如，表：

应该输出:

【解题思路】

对class班级进行分组，再利用having对分组后的数据进行筛选，条件即为:

count(distinct student)>=5

【答案12】

题目13：体育馆人流量

表：Stadium

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列的结果表。查询结果格式如下所示。

【解题思路】

筛选出每行都有>=100的人数记录并不难，直接使用where条件即可，但是关键点在于如何查找出连续三天。

通常情况下，题目中要求查找‘连续三天的数据’，优先考虑使用三张相同的表进行自连接。

三张表a,b,c，假设所求的字段为a.*，则需要将a的id分别设置为三个连续id中的最小值、中间值、最大值，即可得到全部的连续三个值。

【答案13】

题目14：重新格式化部门表

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

部门表 Department：

【解题思路】

对于这种行列互换问题，可采用case....when....结构来判断。

先对id进行分组，当月份month=Jan时，返回对应的revenue数值，else为null，再进行求和，其他月份以此类推。

【答案14】

数据来源：力扣 (LeetCode) - Database

链接：

题库 - 力扣 (LeetCode)​leetcode-cn.com