力扣SQL语句习题，错题记录

不爱健身的数学子弟不是好程序员

已于 2023-01-06 17:06:36 修改

阅读量472

点赞数

文章标签： sql

于 2022-07-26 19:07:52 首次发布

本文链接：https://blog.csdn.net/weixin_55166132/article/details/126000141

版权

本博客是记录力扣SQL习题中错题。👉（原题传送门）👈

1.寻找用户推荐人

给定表 customer ，里面保存了所有客户信息和他们的推荐人。

+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+

写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都不是 2。

对于上面的示例数据，结果为：

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

SQL语句：

IFNULL(expression_1,expression_2);

如果expression_1为NULL，则IFNULL函数返回expression_2; 否则返回expression_1的结果。

SELECT NAME 
FROM
  customer 
WHERE
  ifnull( referee_id, 0 )!=2

2. 计算特殊奖金

写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。返回的结果集请按照employee_id排序。

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

输入：
Employees 表:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3800   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7700   |
+-------------+---------+--------+
输出：
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2           | 0     |
| 3           | 0     |
| 7           | 7400  |
| 8           | 0     |
| 9           | 7700  |
+-------------+-------+
解释：
因为雇员id是偶数，所以雇员id 是2和8的两个雇员得到的奖金是0。
雇员id为3的因为他的名字以'M'开头，所以，奖金是0。
其他的雇员得到了百分之百的奖金。
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

SQL语句：

Mod(a,b) :a / b 的余数;
LEFT(字段名，下标)：返回'字段名'左起'下标'个字符。下标从一开始，不是从零开始。

SELECT
  employee_id,
IF
  ( MOD ( employee_id, 2 )!= 0 AND LEFT ( NAME, 1 )!= 'M', salary, 0 ) bonus 
FROM
  employees 
ORDER BY
  employee_id

3.删除重复邮件

编写一个 SQL 删除语句来删除所有重复的电子邮件，只保留一个id最小的唯一电子邮件。以任意顺序返回结果表。查询结果格式如下所示。

输入: 
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语句：

DELETE p1 
FROM
	Person p1,
	Person p2 
WHERE
	p1.Email = p2.Email 
	AND p1.id > p2.id

4.修复表中的名字

编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。查询结果格式示例如下。

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

MySQL做法

UPPER(str)：将字符串中所有字符转为大写。
LOWER(str)：将字符串中所有字符转为小写。
SUBSTRING(字段名，下标)：从第下标个截取到末尾。注：下标从一开始，不是从零开始
CONCAT：可以将多个字符串拼接在一起。

SELECT
  user_id,
  concat(
	upper(
	LEFT ( NAME, 1 )),
	lower(
	substring( NAME, 2 ))) AS NAME 
FROM
  users 
ORDER BY
  user_id

Oracle做法

INITCAP()函数：将参数的第一个字母变为大写，此外其他的字母则转换成小写。
LOWER()：将参数全部转换为小写字母
UPPER()：将参数全部转换为大写字母

select user_id, INITCAP(name) as name from Users order by user_id

5.按日期分组销售产品

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
查询结果格式如下例所示。

输入：
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)，只需返回该物品名。

MySQL做法

count()：计数
distinct：去掉重复
group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )：将多行合并成一行。

select 
sell_date, 
count(distinct product) as num_sold, 
group_concat(distinct product order by product asc) as products 
from Activities 
group by sell_date 
order by sell_date asc;

Oracle做法

TO_CHAR(字段/字符串,格式)：将前者按照后者的格式转换，前后两者要一一对应。
LISTAGG(字段,分隔符) WITHIN GROUP (ORDER BY 字段 )：行转列函数。
GROUP BY 数字：根据select后面选择的列进行排序；1 就是第一列以此类推。

SELECT 
TO_CHAR(A.sell_date,'YYYY-MM-DD') sell_date,
COUNT(A.PRODUCT) num_sold,
LISTAGG(A.PRODUCT,',') WITHIN GROUP (ORDER BY A.PRODUCT) products
FROM (SELECT DISTINCT sell_date,PRODUCT FROM Activities) A 
GROUP BY TO_CHAR(A.sell_date,'YYYY-MM-DD')
ORDER BY sell_date

6.丢失信息的雇员

写出一个查询语句，找到所有丢失信息的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：

雇员的姓名丢失了，或者
雇员的薪水信息丢失了，或者
返回这些雇员的id employee_id ，从小到大排序。

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

输入：
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释：
雇员1，2，4，5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。

SQL语句：

UNION：将两个查询的结果合并并去重。（列数和字段类型必须一致）
UNION ALL：将两个查询语句的结果直接合并。（列数和字段类型必须一致）

select employee_id from 
(select employee_id from Employees where employee_id not in (select employee_id from Salaries) 
union 
select employee_id from Salaries where employee_id not in (select employee_id from Employees)) a 
order by 
a.employee_id asc

7. 换某种疾病的患者

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

按 任意顺序 返回结果表。查询结果格式如下示例所示。

输入：
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 开头的疾病。

Oracle做法

instr(源字符串,目标字符串,源字符串出现的位置,目标字符串出现的次数)：模糊查询，后两个属性可以不写。

select *
  from Patients p
 where instr(p.conditions, ' DIAB1') > 0
    or instr(p.conditions, 'DIAB1') = 1

不爱健身的数学子弟不是好程序员

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
力扣SQL语句习题，错题记录

力扣SQL语句习题，错题记录1.寻找用户推荐人2. 计算特殊奖金3.删除重复邮件
复制链接

扫一扫