本博客是记录力扣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