Leetcode SQL刷题笔记

Leetcode SQL刷题笔记

没解题方法的都是一眼出的

---

题目：595. 大的国家\Big Countries

题目内容：

World 表：
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
| area        | int     |
| population  | int     |
| gdp         | int     |
+-------------+---------+
name 是这张表的主键。
这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。
 

如果一个国家满足下述两个条件之一，则认为该国是 大国 ：

面积至少为 300 万平方公里（即，3000000 km2），或者
人口至少为 2500 万（即 25000000）
编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。

按 任意顺序 返回结果表。

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

 

示例：

输入：
World 表：
+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+
输出：
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan | 25500100   | 652230  |
| Algeria     | 37100000   | 2381741 |
+-------------+------------+---------+

解题方法：

方法：

select 
	name,
	population,
	area 
from World 
where area >= 3000000 or population >= 25000000

---

题目：1757. 可回收且低脂的产品\Recyclable and Low Fat Products

题目内容：

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
+-------------+---------+
product_id 是这个表的主键。
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。

写出 SQL 语句，查找既是低脂又是可回收的产品编号。

返回结果 无顺序要求 。

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

Products 表：
+-------------+----------+------------+
| product_id  | low_fats | recyclable |
+-------------+----------+------------+
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |
+-------------+----------+------------+
Result 表：
+-------------+
| product_id  |
+-------------+
| 1           |
| 3           |
+-------------+
只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品。

解题方法：

方法：

select product_id from Products where low_fats = 'Y' and  recyclable = 'Y'

---

题目：584. 寻找用户推荐人\Find Customer Referee

题目内容：

给定表 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 |
+------+

解题方法：

方法：

select name from customer where referee_id != 2 or referee_id is null

---

题目：183. 从不订购的客户\Customers Who Never Order

题目内容：

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

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解题方法：

方法：

select 
	name as Customers 
from Customers 
where Customers.Id not in ( select CustomerId from Orders)

---

题目：1873. 计算特殊奖金\Calculate Special Bonus

题目内容：

表: Employees

+-------------+---------+
| 列名        | 类型     |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id 是这个表的主键。
此表的每一行给出了雇员id ，名字和薪水。
 

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

Return the result table ordered by employee_id.

返回的结果集请按照employee_id排序。

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

 

示例 1:

输入：
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。
其他的雇员得到了百分之百的奖金。

解题方法：

方法一：

select
employee_id,
if(
    employee_id&1 and name not like 'M%',
    salary,
    0
) as bonus
from Employees
order by employee_id;

方法二：

select
    employee_id,
    if(
        mod(employee_id, 2)=1 and name not like 'M%',
        salary,
        0
    ) as bonus
from employees
order by employee_id;

方法三：

select 
    employee_id,
    case(
        when employee_id % 2= 1 and left(name,1)!='M' then salary
        else 0
    end) as bonus
from Employees
order by employee_id;

解法讲解：

方法1中 ’ employee_id&1 ’ 为逻辑与运算，1&1=1；0&1=0， 因为奇数尾数数为1，偶数为0，所以奇数与1与运算，结果是1；偶数与1与运算，结果是0。

方法2中 mod(employee_id, 2)=1 为 mod() 函数 ，该函数用来取余数

方法3中 left(name,1) 为 left() 函数，该函数的意思是去从左边往右数第1个字符

方法 1 2 和 3 分别为 if 和 case 的用法

如果我不加 order by 提交代码就会报错可是直接执行缺完全符合，发生这个问题还没找到

---

题目：627. 变更性别\Swap Salary

题目内容：

Salary 表：

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+
id 是这个表的主键。
sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。
 

请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。

注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。

查询结果如下例所示。

 

示例 1:

输入：
Salary 表：
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
+----+------+-----+--------+
输出：
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |
+----+------+-----+--------+
解释：
(1, A) 和 (3, C) 从 'm' 变为 'f' 。
(2, B) 和 (4, D) 从 'f' 变为 'm' 。

解题方法：

方法一：

update Salary set sex = if( sex = 'm','f','m');

方法二：

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

解法讲解：

方法2只能去品真的牛逼

---

题目：1667. 修复表中的名字\Fix Names in a Table

题目内容：

表： Users

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

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

返回按 user_id 排序的结果表。

查询结果格式示例如下。

 

示例 1：

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

解题方法：

方法：

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

解法讲解：

CONCAT() 函数
CONCAT 可以将多个字符串拼接在一起。

LEFT(str, length) 函数
从左开始截取字符串，length 是截取的长度。

UPPER(str) 与 LOWER(str)
UPPER(str) 将字符串中所有字符转为大写
LOWER(str) 将字符串中所有字符转为小写

SUBSTRING(str, begin, end)
截取字符串，end 不写默认为空。
SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。

---

题目：1484. 按日期分组销售产品\Group Sold Products By The Date

题目内容：

表 Activities：

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

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 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)，只需返回该物品名。

解题方法：

方法：

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 

解法讲解：

group_concat() : group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起
来，返回一个字符串结果。
格式:
group_concat([distinct] 字段名[order by 排序字段 asc/desc] [separator ‘分隔符’ ]）
说明:
(1) 使用distinct可以排除重复值;
(2) 如果需要对结果中的值进行排序，可以使用order by子句；
(3) separator是一个字符串值，默认为逗号。

---

模板

题目：XXXX

题目内容：

解题方法：

方法一：

解法讲解：

---

模板

题目：XXXX

题目内容：

解题方法：

方法一：

方法一讲解：

---