高频 SQL 50 题（基础版）- 07. 高级字符串函数 / 正则表达式 / 子句

sjh1
87 阅读13分钟

来源：高频 SQL 50 题（基础版）leetcode.cn/studyplan/s…

1667. 修复表中的名字

表： Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

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

返回按 user_id 排序的结果表。

返回结果格式示例如下。

示例 1：

输入：
Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+
输出：
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

思路 + SQL

这题主要考察的是字符串处理。

  1. CONCAT(...string[]): CONCAT 可以将多个字符串拼接在一起。
  2. LEFT(str, length) 函数 : 从左开始截取字符串，length 是截取的长度。
  3. UPPER(str): 将字符串中所有字符转为大写
  4. LOWER(str) 将字符串中所有字符转为小写
  5. SUBSTRING(str, begin, end): 截取字符串，end 不写默认为空。如：SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。

所以这题的解题思路是，截取第一个字符变成大写，后面的字符变小写。

select
  user_id,
  concat(
    upper(substring(name, 1, 1)),
    lower(substring(name, 2))
  ) as name
from
  Users
order by
  user_id asc;

1527. 患某种疾病的患者

患者信息表： Patients

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
在 SQL 中，patient_id （患者 ID）是该表的主键。
'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。

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

按 任意顺序 返回结果表。

查询结果格式如下示例所示。

示例 1:

输入：
Patients表：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
输出：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
解释：Bob 和 George 都患有代码以 DIAB1 开头的疾病。

思路 + SQL 1 （正则表达式）

这题考察的是正则表达式，需要用正则匹配到疾病代码开头为 DIAB1 的疾病。

使用 \\bDIAB1.*，\b 代表单词边界，所以表示为匹配以 "DIAB1" 开头的字符串。多一个 "" 的原因是用于转义反斜杠（是否需要转义反斜杠看具体编程语言。SQL 需要转义反斜杠）。

select
  *
from
  Patients
where
  conditions regexp '\bDIAB1.*';

思路 + SQL 2 （like 模糊匹配秒了）

可以不用正则表达式，直接 like 解决。

like 'DIAB1%'用于匹配第一个疾病是 I 类糖尿病的情况，like '% DIAB1%'用于匹配后续的疾病是 I 类糖尿病的情况。

select
  *
from
  Patients
where
  conditions like 'DIAB1%'
  or conditions like '% DIAB1%';

196. 删除重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

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

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

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

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

返回结果格式如下示例所示。

示例 1:

输入: 
Person 表:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
输出: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。

思路 + SQL 1

  1. 先尝试使用 SELECT 的方式获取到所有去除最小 id 自身之外的查询情况。
select
  p1.*
from
  Person p1,
  Person p2
where
  p1.email = p2.email
  and p1.id > p2.id;
  1. 然后再改为 DELETE 语句即可。
delete
  p1
from
  Person p1,
  Person p2
where
  p1.email = p2.email
  and p1.id > p2.id;

delete p1 ... from ... where ... 这种写法表达的是，从 p1 表中删除满足where条件的记录。

这流程是带 where 的自连接查询时做删除操作，因此没有笛卡尔积的问题。

  1. 从驱动表（左表）取出 N 条记录；
  2. 拿着这 N 条记录，依次到被驱动表（右表）查找满足 where 条件的记录。

思路 + SQL 2 （更易懂）

虽然用了子查询，但是观感会更好。

  1. 先搜索出每个 email 名称下存在的最小 id 表；
  2. 如果 id 不在上面的表里，说明是重复且需要被删除的。
delete from
  Person
where
  -- 不在最小 id 表里，说明是需要删除的重复电子邮件
  id not in (
    select
      t.id
    from
      -- 最小 id 的唯一电子邮件表
      (
        select
          min(id) as id
        from
          Person
        group by
          email
      ) as t
  );

176. 第二高的薪水

Employee 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
在 SQL 中，id 是这个表的主键。
表的每一行包含员工的工资信息。

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

查询结果如下例所示。

示例 1：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

示例 2：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

思路 + SQL

  1. 这题是考察排序。依据题意，排序的方式是 dense_rank，所以需要对 salary 做去重的操作来获取真正的薪水第二名。
select
  distinct salary as SecondHighestSalary
from
  Employee
order by
  salary desc
limit
  1, 1;
  1. 正常情况下是对的，但如果没有第二名的情况下，需要返回 null 而不是空表。这就需要外面多套一层 select 语句了。select 空查询结果可以得到 null。
select
  (
    select
      distinct salary
    from
      Employee
    order by
      salary desc
    limit
      1, 1
  ) as SecondHighestSalary;
  1. 或者用 ifNull 函数包裹，看起来会更直观：
select
  ifNull(
    (
      select
        distinct salary
      from
        Employee
      order by
        salary desc
      limit
        1, 1
    ), null
  ) as SecondHighestSalary;

1484. 按日期分组销售产品

表 Activities：

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
该表没有主键(具有唯一值的列)。它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。

编写解决方案找出每个日期、销售的不同产品的数量及其名称。

每个日期的销售产品名称应按词典序排列。

返回按 sell_date 排序的结果表。

结果表结果格式如下例所示。

示例 1:

输入：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。

思路

GROUP_CONCAT是 MySQL 中的一个聚合函数，用于将多行数据连接成一个字符串。它的语法如下：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

参数说明：

  • DISTINCT：可选参数，表示去重。
  • expr：需要连接的字段名或表达式。
  • ORDER BY：可选参数，表示排序方式。
  • ASC 或 DESC：可选参数，表示升序或降序排序。
  • SEPARATOR：可选参数，表示连接字符串的分隔符，默认为,。

例如：

GROUP_CONCAT(
    DISTINCT product
    SEPARATOR ','
);

这个函数将去重后的 product 用, 拼接起来了。

SQL

select
  sell_date,
  -- 销售商品的种类
  count(distinct product) as num_sold,
  -- 销售产品名称（用“,”拼接）
  group_concat(
    distinct product
    order by
      product separator ','
  ) as products
from
  Activities
group by
  sell_date
order by
  sell_date asc;

1327. 列出指定时间段内所有的下单产品

表: Products

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| product_id       | int     |
| product_name     | varchar |
| product_category | varchar |
+------------------+---------+
product_id 是该表主键(具有唯一值的列)。
该表包含该公司产品的数据。

表: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| order_date    | date    |
| unit          | int     |
+---------------+---------+
该表可能包含重复行。
product_id 是表单 Products 的外键（reference 列）。
unit 是在日期 order_date 内下单产品的数目。

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

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

查询结果的格式如下。

示例 1:

输入：
Products 表:
+-------------+-----------------------+------------------+
| product_id  | product_name          | product_category |
+-------------+-----------------------+------------------+
| 1           | Leetcode Solutions    | Book             |
| 2           | Jewels of Stringology | Book             |
| 3           | HP                    | Laptop           |
| 4           | Lenovo                | Laptop           |
| 5           | Leetcode Kit          | T-shirt          |
+-------------+-----------------------+------------------+
Orders 表:
+--------------+--------------+----------+
| product_id   | order_date   | unit     |
+--------------+--------------+----------+
| 1            | 2020-02-05   | 60       |
| 1            | 2020-02-10   | 70       |
| 2            | 2020-01-18   | 30       |
| 2            | 2020-02-11   | 80       |
| 3            | 2020-02-17   | 2        |
| 3            | 2020-02-24   | 3        |
| 4            | 2020-03-01   | 20       |
| 4            | 2020-03-04   | 30       |
| 4            | 2020-03-04   | 60       |
| 5            | 2020-02-25   | 50       |
| 5            | 2020-02-27   | 50       |
| 5            | 2020-03-01   | 50       |
+--------------+--------------+----------+
输出：
+--------------------+---------+
| product_name       | unit    |
+--------------------+---------+
| Leetcode Solutions | 130     |
| Leetcode Kit       | 100     |
+--------------------+---------+
解释：
2020 年 2 月份下单 product_id = 1 的产品的数目总和为 (60 + 70) = 130 。
2020 年 2 月份下单 product_id = 2 的产品的数目总和为 80 。
2020 年 2 月份下单 product_id = 3 的产品的数目总和为 (2 + 3) = 5 。
2020 年 2 月份 product_id = 4 的产品并没有下单。
2020 年 2 月份下单 product_id = 5 的产品的数目总和为 (50 + 50) = 100 。

思路 + SQL 1（常规写法）

  1. 先把 2 月份的所有订单查出来：
select
  *
from
  Orders
where
  order_date like '2020-02-%';
  1. 再聚合分组后，筛选出订单量不小于 100 的 product_id：
select
  product_id,
  sum(unit) as unit
from
  Orders
where
  order_date like '2020-02-%'
group by
  product_id
having
  unit >= 100;
  1. 将 product_id 和 Products 表关联起来即可。（可以发现这里使用了子查询，效率会比较低）。
select
  product_name,
  unit
from
  Products p
  inner join (
    -- 订单量不小于 100 的 product_id 表
    select
      product_id,
      sum(unit) as unit
    from
      Orders
    where
      order_date like '2020-02-%'
    group by
      product_id
    having
      unit >= 100
  ) t on p.product_id = t.product_id;

思路 + SQL 2 （using 函数 + date_format 函数）

  1. using 函数的使用

在SQL中，"USING" 是一种用于指定表之间关联条件的函数。它通常用在"JOIN"语句中，用于指定连接表时要使用的列。

通常，在 "JOIN" 语句中，使用"ON"关键字来指定连接条件，例如：

SELECT
  *
FROM
  table1
  JOIN table2 ON table1.column1 = table2.column2;

而使用"USING"函数时，可以简化连接条件的指定，前提是连接的列在两个表中具有相同的名称。例如：

SELECT
  *
FROM
  table1
  JOIN table2 USING (column1);

在上面的例子中，使用 "USING" 函数指定了连接条件。它告诉数据库引擎使用两个表中名为 "column1" 的列进行连接。

使用 "USING" 函数可以使连接条件更加简洁和易读，特别是在连接多个表时。但是需要注意的是，"USING"函数只能用于指定具有相同名称的列进行连接。

  1. DATE_FORMAT 函数使用

DATE_FORMAT 函数是在 SQL 中用于格式化日期的函数。它可以将日期数据以指定的格式进行转换和显示。

DATE_FORMAT 函数的使用方式如下：

DATE_FORMAT(date, format)

其中，date 是要格式化的日期值，可以是一个日期类型的列或者是一个日期值，而 format 是要指定的日期格式。

以下是一些常见的日期格式化的示例：

SELECT
  DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date
FROM
  your_table;

这样会将 date_column 列的日期以 "YYYY-MM-DD" 的格式显示出来。

SELECT
  DATE_FORMAT('2022-03-15', '%W, %M %e, %Y') AS formatted_date;

这样会将 '2022-03-15' 这个日期字符串以 "Weekday, Month Day, Year" 的格式显示出来。

  1. 用 using 函数关联两表，并增加日期条件（这里的日期条件用了 date_format 函数）：
select
  *
from
  Products p
  inner join Orders o using (product_id)
where
  date_format(order_date, '%Y-%m') = '2020-02';

  1. 聚合函数 + having 判断即可得到结果
select
  product_name,
  sum(unit) unit
from
  Products p
  inner join Orders o using (product_id)
where
  date_format(order_date, '%Y-%m') = '2020-02'
group by
  product_name
having
  unit >= 100;

1517. 查找拥有有效邮箱的用户

表: Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+
user_id 是该表的主键（具有唯一值的列）。
该表包含了网站已注册用户的信息。有一些电子邮件是无效的。

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

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

1. 前缀 名称是一个字符串，可以包含字母（大写或小写），数字，下划线 '_' ，点 '.' 和/或破折号 '-' 。前缀名称 必须 以字母开头。

  1. 域 为 '@leetcode.com' 。

以任何顺序返回结果表。

结果的格式如以下示例所示：

示例 1：

输入：
Users 表:
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 2       | Jonathan  | jonathanisgreat         |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
| 5       | Marwan    | quarz#2020@leetcode.com |
| 6       | David     | david69@gmail.com       |
| 7       | Shapiro   | .shapo@leetcode.com     |
+---------+-----------+-------------------------+
输出：
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
+---------+-----------+-------------------------+
解释：
用户 2 的电子邮件没有域。 
用户 5 的电子邮件带有不允许的 '#' 符号。
用户 6 的电子邮件没有 leetcode 域。 
用户 7 的电子邮件以点开头。

思路

这题纯纯的考正则表达式。几个常见的正则表达式模式：

  1. ^：匹配字符串的开始位置。例如，^hello会匹配以"hello"开头的字符串。
  2. $：匹配字符串的结束位置。例如，world$会匹配以"world"结尾的字符串。
  3. .：匹配除换行符以外的任意字符。例如，a.b会匹配"a+b"、"a@b"等。
  4. *：匹配前面的模式零次或多次。例如，a*b会匹配"b"、"ab"、"aab"等。
  5. +：匹配前面的模式一次或多次。例如，a+b会匹配"ab"、"aab"、"aaab"等。
  6. ?：匹配前面的模式零次或一次。例如，a?b会匹配"b"、"ab"。
  7. []：定义字符集合。例如，[abc]会匹配"a"、"b"、"c"中的任意一个字符。
  8. [^]：否定字符集合。例如，[^abc]会匹配除了"a"、"b"、"c"之外的任意字符。
  9. \d：匹配数字。等价于[0-9]。
  10. \w：匹配字母、数字或下划线。等价于[A-Za-z0-9_]。
  11. \s：匹配空白字符，包括空格、制表符、换行符等。
  12. \b：匹配单词边界。例如，\btest\b会匹配单独的单词"test"。

SQL

需要注意的是，.因为匹配任意一个字符，所以如果需要表示句号字符的话，需要 \. 加个反斜杠用于转义。但是在 SQL 中，还需要转义反斜杠本身，所以最终的写法是 \.。

@ 在某些正则表达式中也是有含义的，所以最好转义一下。

  • ^[A-Za-z]: 前缀名称 必须 以字母开头。
  • [A-Za-z0-9_.-]*: 前缀 名称是一个字符串，可以包含字母（大写或小写），数字，下划线 '_' ，点 '.' 和/或破折号 '-' （非开头情况）。
  • \\@leetcode\\.com$: 域为 '@leetcode.com' （也就是以 '@leetcode.com' 做结尾）。
select
  *
from
  Users
where
  mail regexp '^[A-Za-z][A-Za-z0-9_.-]*\@leetcode\.com$';
avatar
文章
阅读
粉丝