leetcode的SQL入门题

题集如下：
https://leetcode-cn.com/study-plan/sql/

第一天：选择

595.大的国家

https://leetcode-cn.com/problems/big-countries/

我的题解：

# Write your MySQL query statement below
select name,population,area from World
where area>=3000000 or population>=25000000

结果：

5 / 5 个通过测试用例
状态：通过
执行用时: 198 ms
内存消耗: 0 B

1757. 可回收且低脂的产品

https://leetcode-cn.com/problems/recyclable-and-low-fat-products/

我的题解：

# Write your MySQL query statement below
select product_id from Products where low_fats = 'Y' and recyclable = 'Y'

结果：
执行结果：
通过

执行用时：620 ms
, 在所有 MySQL 提交中击败了19.46%的用户

内存消耗：0 B
, 在所有 MySQL 提交中击败了100.00%的用户

通过测试用例：
22 / 22

584. 寻找用户推荐人

https://leetcode-cn.com/problems/find-customer-referee/

我的题解：

# Write your MySQL query statement below
select name from customer
where referee_id != 2 or referee_id is NULL

执行结果：

通过

显示详情

执行用时：474 ms, 在所有 MySQL 提交中击败了84.25%的用户

内存消耗：0 B, 在所有 MySQL 提交中击败了100.00%的用户

通过测试用例：19 / 19

183. 从不订购的客户

https://leetcode-cn.com/problems/customers-who-never-order/

# Write your MySQL query statement below
select Customers.Name as Customers from Customers
where Customers.Id not in (select CustomerId from Orders)

执行结果：

通过

显示详情

执行用时：540 ms, 在所有 MySQL 提交中击败了70.44%的用户

内存消耗：0 B, 在所有 MySQL 提交中击败了100.00%的用户

通过测试用例：11 / 11

第二天 排序 & 修改

1873. 计算特殊奖金

https://leetcode-cn.com/problems/calculate-special-bonus/

方法一、UNION + NOT LIKE

SELECT employee_id ,salary AS bonus
FROM Employees
WHERE employee_id%2!=0 AND name NOT LIKE ('M%')
UNION 
SELECT employee_id ,salary*0 AS bonus
FROM Employees
WHERE employee_id%2=0 OR name LIKE ('M%')
ORDER BY employee_id;

方法二、IF +RIGHT +MOD

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

方法三、CASE+RIGHT +MOD

SELECT employee_id,
(CASE WHEN MOD(employee_id,2)!=0 AND LEFT(name,1)!='M' THEN salary
     WHEN MOD(employee_id,2)=0 OR LEFT(name,1)='M' THEN 0
END) bonus
FROM Employees
ORDER BY employee_id

方法四、CASE WHEN THEN

SELECT employee_id,
    (CASE WHEN MOD(employee_id,2)!=0 AND name NOT LIKE 'M%' THEN salary ELSE 0 END) AS bonus
FROM Employees

CASE的教程：https://www.bilibili.com/video/BV1Cu411Z74k/

627. 变更性别

https://leetcode-cn.com/problems/swap-salary/

方法一、CASE WHEN THEN

update salary
set sex = (
    case sex when 'm' then 'f' else 'm' end
);

方法二、if

update salary set sex = if(sex = 'm','f','m')

方法三、ASCII码

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

196. 删除重复的电子邮箱

https://leetcode-cn.com/problems/delete-duplicate-emails/

delete u
from Person u , Person v
where v.id < u.id and u.email = v.email