SQL学习计划
我的力扣主页：https://leetcode.cn/u/youthful-6aussnpr/
关于如何构建测试数据库：
首先到工作台构建一个测试数据库，然后回到力扣题目界面，点击SQL架构，将其中内容悉数复制到工作台，然后在每条语句后面加上分号，执行，即可在工作台中生成测试用表，有时候还有额外的地方需要自己修改，比如把'None'改成NULL
注意：无法在网页测试时创建索引提高效率，that’s a pity！
建议大家创建一个查询效率的存储过程（stored procedure）
DELIMITER $$
CREATE PROCEDURE get_last_query_cost()
BEGIN
SHOW STATUS LIKE 'last_query_cost';
END$$
DELIMITER ;
然后执行完一段Query，就可以看看这个Query的value是多少，value越低越好！
第一天：选择
595 大的国家（简单题）
吐槽：这题结构有问题，gdp不能为int类型，应该为double，否则会越界（已测试）
https://leetcode.cn/problems/big-countries/
# Write your MySQL query statement below
# 两种解法速度相差无几，毕竟数据量小
# 但是今天的主题是选择，指明官方更希望我们用OR来解这道题
# 同时用OR也更简洁
-- 解法1：WHERE + OR
SELECT
name,population,area
FROM
world
WHERE
area>=3000000 OR population >=25000000;
-- 解法2：WHERE + UNION
# SELECT
# name, population, area
# FROM
# world
# WHERE
# area >= 3000000
# UNION
# SELECT
# name, population, area
# FROM
# world
# WHERE
# population >= 25000000
# ;
#关于索引：最佳的选择应该是两种方法都进行尝试比较（OR和UNION）。
1757 可回收且低脂的产品（简单题）
https://leetcode.cn/problems/recyclable-and-low-fat-products/
# Write your MySQL query statement below
SELECT
product_id
FROM
Products
WHERE
low_fats = 'Y' AND recyclable = 'Y';
584 寻找用户推荐人（简单题）
https://leetcode.cn/problems/find-customer-referee/
# Write your MySQL query statement below
-- 解法1:OR
-- 速度最快，查询了6行（使用EXLPALIN在数据库测试结果）
SELECT
name
FROM
customer
WHERE
referee_Id <> 2 OR referee_Id IS NULL;
-- 解法2:UNION ALL
-- 查询了12行
# SELECT
# name
# FROM
# customer
# WHERE
# referee_id <> 2
# UNION ALL
# SELECT
# name
# FROM
# customer
# WHERE
# referee_id IS NULL;
-- 解法3:双层SELECT
-- 查询了12行
# SELECT
# name
# FROM
# customer
# WHERE id NOT IN (
# SELECT id
# FROM customer
# WHERE referee_id = 2);
-- 解法4:使用EXISTS筛选
-- 查询了13行
# SELECT C.name
# FROM customer C
# WHERE NOT EXISTS (
# SELECT C1.name
# FROM customer C1
# WHERE C1.id = C.id
# AND C1.referee_id = 2);
183 从不订购的客户（简单题）
https://leetcode.cn/problems/customers-who-never-order/
# Write your MySQL query statement below
-- 在这里三种解法效率一模一样
-- 解法1:双层SELECT
SELECT Name AS Customers
FROM Customers
WHERE Id NOT IN(SELECT CustomerId FROM Orders);
-- 解法2:左连接
# SELECT
# name
# FROM
# customers
# LEFT JOIN orders
# ON customers.id = orders.customerId
# WHERE ISNULL(customerId);
-- 解法3:右连接，本质上和解法2一样
# SELECT
# name
# FROM
# orders
# RIGHT JOIN customers
# ON customers.id = orders.customerId
# WHERE ISNULL(customerId);
第 2 天：排序 & 修改
1873 计算特殊奖金（简单题）
https://leetcode.cn/problems/calculate-special-bonus/
# Write your MySQL query statement below
# SELECT
# employee_id,
# salary AS bonus
# FROM Employees
# WHERE name NOT LIKE 'M%' AND employee_id%2 <> 0
# UNION
# SELECT employee_id ,salary*0 AS bonus
# FROM Employees
# WHERE employee_id%2=0 OR name LIKE ('M%')
# ORDER BY employee_id;
# `IF`第一个参数写条件，第二个参数写条件成立返回的内容，第三个参数写条件不成立返回的内容
# Mod(a,b) 在sql中的意思是 a / b 的余数
# 基础用法：如果id需要是偶数或者奇数时就可以使用mod。
# mod(id,2)=1 是指id是奇数。
# mod(id,2)=0 是指id是偶数。
# SELECT
# employee_id,
# IF(MOD(employee_id,2)!=0 AND LEFT(name,1)!='M',salary,0) bonus
# FROM Employees
# ORDER BY employee_id
# CASE`配合`WHEN`,`THEN`使用，`WHEN`后接条件，`THEN`后接符合条件返回的内容 ，有多个条件时使用
# 需要用`ELSE`返回以上条件都不成立时返回的内容，最后以`END`结尾
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
ELSE 0 -- 这里ELSE可以省略
END AS bonus
FROM Employees
ORDER BY employee_id
627 变更性别（简单题）
https://leetcode.cn/problems/swap-salary/
# Write your MySQL query statement below
UPDATE Salary
SET sex=IF(sex = 'm','f','m');
# 感觉这里用case有些鸡肋，case个人认为适用于多个选择
# UPDATE salary
# SET
# sex = CASE sex
# WHEN 'm' THEN 'f'
# ELSE 'm'
# END;
196 删除重复的电子邮箱（简单题）
https://leetcode.cn/problems/delete-duplicate-emails/
-- 解法1:类似自连接了，在这里效率极低，删除3000行代码花三秒多
# DELETE p1
# FROM
# Person p1,
# Person p2
# WHERE
# p1.Email = p2.Email AND p1.Id > p2.Id;
-- 解法2:双重SELECT，效率高，删除3000行代码花0.25秒
DELETE FROM Person
WHERE Id NOT IN (
SELECT Id
FROM(
SELECT MIN(Id) AS id
FROM Person
GROUP BY Email
) derived_table
)
# 执行用时：
# 581 ms
# , 在所有 MySQL 提交中击败了
# 90.71%
# 的用户
第 3 天：字符串处理函数/正则
1667 修复表中的名字（简单题）
https://leetcode.cn/problems/fix-names-in-a-table/
# Write your MySQL query statement below
# CONACT(str1,str2)————拼接两个字符串
# UPPER(str)————字符串大写
# LOWER(str)————字符串小写
# LENGTH(str)————字符串长度
# SUBSTRING(str,start,end)————截取字符串,start开始,end结束。
# LEFT(str,len)————从左边开始截取字符串
# RIGHT(str,len)————从右边开始截取字符串
SELECT
user_id,
CONCAT(
UPPER(LEFT(name, 1)),
LOWER(SUBSTRING(name, 2))
) AS name
FROM Users
ORDER BY user_id
1484 按日期分组销售产品（简单题）
https://leetcode.cn/problems/group-sold-products-by-the-date/
# Write your MySQL query statement below
# 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 SEPARATOR ',') AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date
;
1527 患某种疾病的患者（简单题）
https://leetcode.cn/problems/patients-with-a-condition/
测试了九万多行代码，影响行数三万多
测试了很多遍，也有正则速度更快的时候，但总体上是LIKE比正则速度更快
-- 解法1:OR + LIKE
SELECT * FROM Patients
WHERE conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%';
-- 解法2:正则
SELECT * FROM PATIENTS
WHERE CONDITIONS REGEXP '^DIAB1|\\sDIAB1';
第 4 天：组合查询 & 指定选取
1965 丢失信息的雇员（简单题）
https://leetcode.cn/problems/employees-with-missing-information/
# Write your MySQL query statement below
#方法一：双重SELECT
# 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)
# ORDER BY employee_id ASC
#方法二：LEFT JOIN
SELECT A.employee_id
FROM employees A
LEFT JOIN salaries B
ON A.employee_id = B.employee_id
WHERE B.salary IS NULL
UNION
SELECT A.employee_id
FROM salaries A
LEFT JOIN employees B
ON A.employee_id = B.employee_id
WHERE B.name IS NULL
ORDER BY employee_id ASC
#使用SHOW STATUS LIKE 'last_query_cost';测试，方法二效率比方法一高许多
1795 每个产品在不同商店的价格（简单题）
报表系统中经常需要行列转换，在 SQL Server 等数据库中可以用 PIVOT 、UNPIVOT 来实现，但是在 MySQL 数据库中却不支持，太傻了，太傻了，找到了教程，测试案例已经写完了，却发现根本执行不了，为什么其他数据库支持MySQL不支持？？？
今天是8月19号晚上8:06，写到这里的时候，正在下载Oracle，待更新这题的题解
这个题，如果有多个store，MySQL直接无解
https://leetcode.cn/problems/rearrange-products-table/
# Write your MySQL query statement below
# 执行速度不行 ，存在更优解
# SELECT product_id,'store1' AS store ,store1 AS price FROM Products WHERE store1 IS NOT NULL
# -- 下面省略了两个AS
# UNION
# SELECT product_id,'store2',store2 FROM Products WHERE store2 IS NOT NULL
# UNION
# SELECT product_id,'store3',store3 FROM Products WHERE store3 IS NOT NULL
# # ORDER BY product_id ASC;
608 树节点（中等题）
https://leetcode.cn/problems/tree-node/
-- 解法1:使用 CASE WHEN
-- 使用SHOW STATUS LIKE 'last_query_cost';测试value=1.498，效率最高
SELECT id ,
CASE
WHEN p_id IS NULL THEN "Root"
WHEN id IN (SELECT DISTINCT p_id FROM tree) THEN "Inner"
ELSE "Leaf"
END TYPE
FROM tree;
-- 解法2:使用 CASE WHEN
-- 使用SHOW STATUS LIKE 'last_query_cost';测试value=2.247，效率一般
# SELECT
# id AS `Id`,
# CASE
# WHEN tree.id = (SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL)
# THEN 'Root'
# WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
# THEN 'Inner'
# ELSE 'Leaf'
# END AS Type
# FROM tree
-- 解法3:UNION
-- 使用SHOW STATUS LIKE 'last_query_cost';测试value=4.946048，效率最低
# SELECT
# id,
# 'Root' AS Type
# FROM tree
# WHERE
# p_id IS NULL
# UNION
# SELECT
# id,
# 'Inner' AS Type
# FROM tree
# WHERE
# id IN (
# SELECT DISTINCT
# p_id
# FROM
# tree
# WHERE
# p_id IS NOT NULL)
# AND p_id IS NOT NULL
# UNION
# SELECT
# id,
# 'Leaf' AS Type
# FROM tree
# WHERE
# id NOT IN (
# SELECT DISTINCT
# p_id
# FROM
# tree
# WHERE
# p_id IS NOT NULL)
# AND p_id IS NOT NULL
# ORDER BY id;
176 第二高的薪水（中等题）
https://leetcode.cn/problems/second-highest-salary/
# Write your MySQL query statement below
-- 解法1:LIMIT，和解法3效率一样
-- value = 2.449
# SELECT (
# SELECT
# DISTINCT salary
# FROM Employee
# ORDER BY salary DESC
# LIMIT 1,1
# ) AS SecondHighestSalary;
-- 解法2:WHERE，效率最高
-- value = 0.898
SELECT
MAX(DISTINCT salary) AS SecondHighestSalary
FROM Employee
WHERE salary<(
SELECT MAX(salary)
FROM Employee
);
-- 解法3: IFNULL
-- 这是一个错误案例，因为如果第二不存在，该解返回的是无内容，而不是NULL
# SELECT DISTINCT salary SecondHighestSalary
# FROM Employee
# ORDER BY salary desc
# LIMIT 1,1;
# # 我们把上述解套上IFNULL
# SELECT IFNULL(
# (SELECT DISTINCT salary
# FROM Employee
# ORDER BY salary desc
# LIMIT 1,1)
# ,Null) AS SecondHighestSalary
第 5 天：合并
175 组合两个表
https://leetcode.cn/problems/combine-two-tables/
-- sql左连接和右连接区别是：左连接会读取左边数据表的全部数据，即使右边数据表没有对应数据；而右连接会读取右边数据表的全部数据，即使左边数据表没有对应数据
-- 多表外连接即用外连接的方式，连接多个表，在使用过程中，尽量避免使用`RIGHT JOIN`，最好都适用`LEFT JOIN`，因为在一个Query中同时使用`LEFT`，`RIGHT`，及`INNER JOIN`时，情况会变得很复杂，可读性差
# Write your MySQL query statement below
-- 两种解法本质相同，即效率一模一样
-- 左连接
# SELECT
# FirstName, LastName, City, State
# FROM Person
# LEFT JOIN Address
# ON Person.PersonId = Address.PersonId;
-- 右连接
SELECT
FirstName, LastName, City, State
FROM Address
RIGHT JOIN Person
ON Person.PersonId = Address.PersonId;
1581 进店却未进行过交易的顾客
https://leetcode.cn/problems/customer-who-visited-but-did-not-make-any-transactions/
# SELECT customer_id,COUNT(customer_id) AS count_no_trans
-- 解法1:NOT IN + 双层SELECT
SELECT
customer_id,
COUNT(customer_id) count_no_trans
FROM Visits
WHERE visit_id NOT IN( SELECT
visit_id
FROM Transactions)
GROUP BY customer_id;
-- 解法2:IS NULL +JOIN
SELECT customer_id, COUNT(customer_id) count_no_trans
FROM Visits
LEFT JOIN Transactions
ON Visits.visit_id = Transactions.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id;
1148 文章浏览 I
https://leetcode.cn/problems/article-views-i/
-- 解法1和解法2效率相同
-- 解法1:DISTINCT
SELECT DISTINCT author_id id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id
-- 解法2：GROUP BY
# SELECT author_id AS 'id'
# FROM Views
# WHERE author_id = viewer_id
# GROUP BY id
# ORDER BY id
第 6 天：合并
197 上升的温度
https://leetcode.cn/problems/rising-temperature/
# Write your MySQL query statement below
-- 两者效率相同，个人认为解法1可读性更高
-- 解法1:FROM选择双表
SELECT w1.id
FROM Weather w1,Weather w2
WHERE DATEDIFF(w1.recordDate,w2.recordDate)=1
AND w1.temperature > w2.temperature;
-- 反着来也行
# SELECT w2.Id
# FROM Weather w1, Weather w2
# WHERE DATEDIFF(w2.recordDate, w1.recordDate) = 1
# AND w1.Temperature < w2.Temperature;
-- 解法2：通过JOIN
SELECT
w1.id
FROM Weather w1
JOIN Weather w2
ON DATEDIFF(w1.recordDate,w2.recordDate)=1
AND w1.temperature>w2.temperature;
607 销售员
https://leetcode.cn/problems/sales-person/
# Write your MySQL query statement below
SELECT
s.name
FROM
salesperson s
WHERE
s.sales_id NOT IN (
SELECT
o.sales_id
FROM
orders o
LEFT JOIN
company c
ON o.com_id = c.com_id
WHERE
c.name = 'RED')
;
第 7 天：计算函数
1141 查询近30天活跃用户数
https://leetcode.cn/problems/user-activity-for-the-past-30-days-i/
# Write your MySQL query statement below
SELECT
activity_date day,
COUNT(DISTINCT user_id) active_users
FROM Activity
WHERE DATEDIFF('2019-07-27',activity_date)<30
AND DATEDIFF('2019-07-27',activity_date)>0 -- 截至 2019-07-27（包含2019-07-27）
GROUP BY activity_date;
1693 每天的领导和合伙人
https://leetcode.cn/problems/daily-leads-and-partners/
# Write your MySQL query statement below
select date_id , make_name ,count(distinct lead_id) unique_leads , count(distinct partner_id) unique_partners
from dailysales
group by date_id,make_name;
-- ID这里为啥会有0，迷惑，对题目输出困惑了五六分钟，才发现0也是id，之前以为id为0 就是NULl害
1729 求关注者的数量
https://leetcode.cn/problems/find-followers-count/
# Write your MySQL query statement below
SELECT user_id ,COUNT(follower_id) followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;
第 8 天：计算函数
586 订单最多的客户
https://leetcode.cn/problems/customer-placing-the-largest-number-of-orders/
# Write your MySQL query statement below
-- 正常题解
SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(order_number) DESC
LIMIT 1;
-- 使用HAVING，ORDER BYBY，LIMIT1，可以解决两个最
# SELECT
# customer_number
# FROM
# Orders
# GROUP BY customer_number
# HAVING COUNT(*) = (
# SELECT
# COUNT(customer_number) AS 'cnt'
# FROM
# Orders
# GROUP BY customer_number
# ORDER BY cnt DESC
# LIMIT 1
# );
-- 使用HAVING ALL同样可以解决两个最
# SELECT customer_number
# FROM Orders
# GROUP BY customer_number
# HAVING COUNT(*) >= ALL(
# SELECT COUNT(*) AS cnt
# FROM Orders
# GROUP BY customer_number
# );
511 游戏玩法分析 I
https://leetcode.cn/problems/game-play-analysis-i/
# Write your MySQL query statement below
select player_id,min(event_date) first_login
from activity
group by player_id
# select distinct player_id,min(event_date) over(partition by player_id) as first_login
# from Activity
1890 2020年最后一次登录
https://leetcode.cn/problems/the-latest-login-in-2020/
# Write your MySQL query statement below
SELECT user_id, max(time_stamp) last_stamp
FROM Logins
WHERE year(time_stamp) = 2020
GROUP BY user_id
1741 查找每个员工花费的总时间
https://leetcode.cn/problems/find-total-time-spent-by-each-employee/
# Write your MySQL query statement below
SELECT
event_day day,
emp_id,
SUM(out_time-in_time) total_time
FROM Employees
GROUP BY day,emp_id;
第 9 天：控制流
1393 股票的资本损益（中等题）
https://leetcode.cn/problems/capital-gainloss/
# Write your MySQL query statement below
-- SUM + CASE WHEN
# SELECT stock_name, SUM(CASE WHEN operation="Buy" then -price else price end) capital_gain_loss
# FROM Stocks
# GROUP BY stock_name;
-- SUM + IF
SELECT stock_name, SUM(IF(operation="Buy",-price,price)) capital_gain_loss
FROM Stocks
GROUP BY stock_name;
-- 两层SELECT
# SELECT
# s1.stock_name,
# s2.sell-s1.buy capital_gain_loss
# FROM (
# SELECT
# stock_name,
# SUM(price) buy
# FROM Stocks
# WHERE operation="Buy"
# GROUP BY stock_name) AS s1,
# (
# SELECT
# stock_name,
# SUM(price) sell
# FROM Stocks
# WHERE operation="Sell"
# GROUP BY stock_name) AS s2
# WHERE s1.stock_name=s2.stock_name
1407 排名靠前的旅行者
https://leetcode.cn/problems/top-travellers/
# Write your MySQL query statement below
# SELECT
# name,
# IFNULL(travelled_distance,0) AS travelled_distance
# FROM Users
# LEFT JOIN(
# SELECT
# user_id,
# SUM(distance) travelled_distance
# FROM Rides
# GROUP BY user_id) x
# ON Users.id = x.user_id
# ORDER BY travelled_distance DESC,name;
SELECT
name,
IFNULL(SUM(distance),0) travelled_distance
FROM Users u
LEFT JOIN Rides r ON u.id = r.user_id
GROUP BY u.id,name
ORDER BY travelled_distance DESC, name;
1158 市场分析 I（中等题）
https://leetcode.cn/problems/market-analysis-i/
# Write your MySQL query statement below
-- 官方解
# select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019
# from Users
# left join (
# select buyer_id, count(order_id) cnt
# from Orders
# -- where order_date between '2019-01-01' and '2019-12-31'
# where YEAR(order_date)=2019
# group by buyer_id
# ) UserBuy
# on Users.user_id = UserBuy.buyer_id;
SELECT user_id buyer_id,join_date,COUNT(order_id) orders_in_2019
FROM users
LEFT JOIN orders
ON user_id = buyer_id AND YEAR(order_date)='2019'
GROUP BY user_id,join_date;
# SELECT user_id buyer_id,join_date,COUNT(buyer_id) orders_in_2019
# FROM users u
# LEFT JOIN orders o ON YEAR(o.order_date)='2019' AND u.user_id=o.buyer_id
# GROUP BY user_id,join_date;
第 10 天 过滤
182 查找重复的电子邮箱
https://leetcode.cn/problems/duplicate-emails/
# Write your MySQL query statement below
-- 效率：1>3>2
-- 解法1
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email)>1;
-- 解法2
# SELECT Email FROM (SELECT COUNT(1) AS T,Email FROM Person GROUP BY Email) r WHERE r.T>1;
-- 解法3
# SELECT DISTINCT(P1.Email) FROM Person P1
# JOIN Person P2 ON p1.Email = p2.Email AND p1.Id!=p2.Id;
1050 合作过至少三次的演员和导演
https://leetcode.cn/problems/actors-and-directors-who-cooperated-at-least-three-times/
# Write your MySQL query statement below
SELECT
actor_id,
director_id
FROM ActorDirector
GROUP BY actor_id,director_id
-- 选出合作过的导演和演员
HAVING COUNT(*)>=3
-- 筛选出合作次数大于等于3的组合
1587 银行账户概要 II
https://leetcode.cn/problems/bank-account-summary-ii/
# Write your MySQL query statement below
SELECT
name,
SUM(amount) balance
FROM Users
LEFT JOIN Transactions USING(account)
GROUP BY name
HAVING balance>=10000
1084 销售分析III
https://leetcode.cn/problems/sales-analysis-iii/
# Write your MySQL query statement below
SELECT p.product_id,product_name FROM sales s,product p
WHERE s.product_id=p.product_id
GROUP BY p.product_id
HAVING SUM(sale_date < '2019-01-01')=0
AND SUM(sale_date>'2019-03-31')=0;
237
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
