【SQL高频练习带刷】day8：高级字符串函数 / 正则表达式 / 子句

原创已于 2024-07-27 19:50:27 修改 · 889 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#sql #算法 #数据库

于 2024-07-27 19:49:39 首次发布

SQL习题带刷专栏收录该内容

44 篇文章

订阅专栏

题目一：修复表中的名字

题目要求：

编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。

表结构：

运行结果示例：

思路：

本题考察了SQL中常用的几个字符串函数：

CONCAT 用来拼接字符串 concat（s1,s2,s3,....）
LEFT 从左边截取字符 lfet(s,1)
RIGHT 从右边截取字符 right("ABC",1)
UPPER 变为大写 upper("ade")
LOWER 变为小写 lower("ASA")
LENGTH 获取字符串长度 length("asdse")

运行代码示例：

select user_id,CONCAT(UPPER(LEFT(name,1)), LOWER(right(name,LENGTH(name) -1))) as name
from Users
ORDER BY user_id

题目二：患某种疾病的患者

题目要求：

查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。

按 任意顺序 返回结果表。

表结构：

运行结果示例：

思路：

这道题直接使用正则匹配就可以，也可以简单点用like关键字，但是需要注意，不能用“%DIAB1%”去做，因为题目要求必须以DIAB1作为开头，这又包括了它是第一个单词开头或者非第一个单词的单词开头的情况。我们可以用“conditions like "DIAB1%" or conditions like "% DIAB1%"”来做。

运行代码示例：

select patient_id , patient_name , conditions
from Patients
where conditions like "DIAB1%" or conditions like "% DIAB1%"

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

题目要求：

编写解决方案删除所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。

（对于 SQL 用户，请注意你应该编写一个 DELETE 语句而不是 SELECT 语句。）

（对于 Pandas 用户，请注意你应该直接修改 Person 表。）

运行脚本后，显示的答案是 Person 表。驱动程序将首先编译并运行您的代码片段，然后再显示 Person 表。Person 表的最终顺序 无关紧要 。

表结构：

运行结果示例：

思路：

首先，我们要明确一下删除语句。
DELETE FROM 表名称 WHERE 列名称 = 值
之后我们只需要考虑如何筛选出“邮箱值重复”且“id不为最小”的数据。emm好像没什么思路，那么我们反向思考，寻找“邮箱唯一”或“邮箱重复且id最小”的数据id，似乎可以考虑一下group by子句？我们按照邮箱分组，就可以得到所有重复/不重复的邮箱，再从每组中找到最小的id不就行了？如果邮箱唯一，也满足上述条件，似乎没什么问题。得到这些id后，我们只需要删除不在这些id序列中的id数据即可。

但是当我们按照如上思路写出代码时，会报错，提示delete 的where中不能有子查询。所以我们需要给(select min(Id) as id from Person group by Email)赋个别名A，再从A表中获取id。
delete from Person
where id not in 
(select min(Id) as id from Person group by Email) 

运行代码示例：

delete from Person
where id not in (
    select need.id from (select min(Id) as id from Person group by Email) as need)

题目四：第二高的薪水

题目要求：

查询并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

表结构：

运行结果示例：

思路：

这道题目难度虽然是中等但感觉还好....可能是因为小的细节点太多了。比如首先筛选出第二高的薪资，就需要使用order by和limit进行；其次在排序时，我们要对相同的值进行去重；最后就是如果不存在第二高的薪水，我们要使用ifnull再进行一个判断，将返回值改为null。

运行代码示例：

# Write your MySQL query statement below
select ifnull((select distinct salary 
from Employee
order by salary desc
limit 1,1),null) SecondHighestSalary

题目五：按日期分组销售产品

题目要求：

编写解决方案找出每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。

表结构：

运行结果示例：

思路：

这道题考察是主要是分组拼接函数 group_concat()。使用方法是

GROUP_CONCAT(
DISTINCT expression1
ORDER BY expression2
SEPARATOR sep
);

运行代码示例：

select sell_date,count(distinct product) num_sold , group_concat(
        distinct product
        order by product
        separator ','
    ) products
from Activities
group by sell_date
order by sell_date

题目六：列出指定时间段内所有下单产品

题目要求：

写一个解决方案，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。

返回结果表单的 顺序无要求 。

表结构：

运行结果示例：

思路：

这道题目并不难，主要是对时间条件的筛选如何做，我们使用大小比较、like、函数转换都可以。此外还要注意对于unit的比较要放在having子句中进行。

运行代码示例：

select product_name,sum(unit) as unit
from Orders
left join Products
using(product_id)
where order_date between '2020-02-01' and '2020-02-29'
group by product_name
having sum(unit)>=100

题目七：查找拥有有效邮箱的用户

题目要求：

编写一个解决方案，以查找具有有效电子邮件的用户。

一个有效的电子邮件具有前缀名称和域，其中：

前缀名称是一个字符串，可以包含字母（大写或小写），数字，下划线 '_' ，点 '.' 和/或破折号 '-' 。前缀名称必须以字母开头。
域为 '@leetcode.com' 。
以任何顺序返回结果表。

表结构：

运行结果示例：

思路：

题目考察的是正则表达式。

^：这个符号表示字符串的开始。
[a-zA-Z]：这个部分匹配任何单个字母，无论大小写。
[a-zA-Z0-9_.-]：这个部分匹配任何数量（包括零个）的字母，数字，下划线，点或破折号。星号表示前面的字符可以出现任意次数。
\@leetcode\.com：这个部分匹配"@leetcode.com"字符串。在这里，\用于转义特殊字符@和.，使它们被视为普通字符而不是正则表达式的特殊符号。
$：这个符号表示字符串的结束。

运行代码示例：

SELECT user_id, name, mail
FROM Users
-- `@`字符需要转义，因为它在某些正则表达式中具有特殊意义
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';