LeetCode_专项突破之SQL入门

595. 大的国家

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

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

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

union 

select name,population,area
from World
where population >= 25000000;

# ============================================

select name,population,area
from World 
where area >= 3000000 or population population >= 25000000;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

584. 寻找用户推荐人

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

SELECT name
FROM customer
WHERE referee_id IS NULL or referee_id <> 2;

# ============================================

SELECT name
FROM customer
WHERE referee_id IS NULL or referee_id != 2;
1
2
3
4
5
6
7
8
9

183. 从不订购的客户

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

NOT IN 子查询不在此列表中的客户

SELECT Customers.Name as 'Customers' 
FROM Customers
WHERE Customers.id not in(
    SELECT CustomerId FROM Orders
)
1
2
3
4
5

627. 变更性别

UPDATE Salary SET sex = IF(sex = 'f','m','f')

UPDATE Salary
SET sex = 
    CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;
1
2
3
4
5
6
7
8

196. 删除重复的电子邮箱

思路一、将表进行自连接，p1 id大的删除
思路二、将原来的表email去重，相同的保留id较小的作为临时表，如果原表id 不在这个表中，就是重复的，删除即可

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

# ============================================

DELETE FROM Person
WHERE id not in(
    SELECT * FROM (SELECT min(id) FROM Person GROUP BY email)
    as p1
)

1
2
3
4
5
6
7
8
9
10
11
12

176. 第二高的薪水

https://leetcode.cn/problems/second-highest-salary/?envType=study-plan&id=sql-beginner

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

# 小于最大的中最大的就是第二大

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee)

SELECT
    (SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 ,1) AS SecondHighestSalary 

SELECT
    IFNULL((SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1),NULL) AS SecondHighestSalary
1
2
3
4
5
6
7
8
9
10
11

608. 树节点

https://leetcode.cn/problems/tree-node/?envType=study-plan&id=sql-beginner
给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
方案一、使用自连接，CASE分类出各个节点

# 子查询
SELECT id,
    CASE
        WHEN p_id IS NULL
            THEN 'Root'
        WHEN id IN(SELECT p_id FROM tree)
            THEN 'Inner'
        else 'Leaf'
        END AS Type
FROM tree
ORDER BY id;
1
2
3
4
5
6
7
8
9
10
11

方案二、联合查询

# UNION 自带去重 UNION ALL 不去重

SELECT id,'Root' AS Type
FROM tree
WHERE p_id IS 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

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
ORDER BY id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

197. 上升的温度

https://leetcode.cn/problems/rising-temperature/
编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 不要求顺序 。

# DATEDIFF(date1,date2) 返回的是 data1 - date2 所的的日期
SELECT Weather.id AS id
FROM Weather JOIN Weather w ON DATEDIFF(Weather.recordDate,w.recordDate) = 1 AND Weather.Temperature > w.Temperature
1
2
3

607. 销售员

https://leetcode.cn/problems/sales-person/

编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
以 任意顺序 返回结果表。

解题思路：不断嵌套子查询或者采用左连接简化子查询

SELECT SalesPerson.name
FROM SalesPerson
WHERE SalesPerson.sales_id NOT IN (
    SELECT Orders.sales_id FROM Orders WHERE Orders.com_id = (SELECT com_id FROM Company WHERE name = 'RED')
)
===============================================
SELECT SalesPerson.name
FROM SalesPerson
WHERE SalesPerson.sales_id NOT IN (
    SELECT Orders.sales_id FROM Orders LEFT JOIN Company ON Orders.com_id = Company.com_id
    WHERE Company.name = 'RED'
)
1
2
3
4
5
6
7
8
9
10
11
12

586. 订单最多的客户

编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。
测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

对用户进行分组，计算数量降序排列，取最大值

# 分组聚合降序选 1 个
SELECT customer_number 
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;
1
2
3
4
5
6

511. 游戏玩法分析 I

写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。

分组查询，第一次登录就采取日期最小的

SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id
1
2
3

1890. 2020年最后一次登录

编写一个 SQL 查询，该查询可以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。返回的结果集可以按 任意顺序 排列。

注意year 函数返回当年年份，where > group by > order by

# 注意year 函数返回当年年份，where > group by > order by
SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE YEAR(time_stamp) = '2020'
GROUP BY user_id
1
2
3
4
5

1741. 查找每个员工花费的总时间

编写一个SQL查询以计算每位员工每天在办公室花费的总时间（以分钟为单位）。 请注意，在一天之内，同一员工是可以多次进入和离开办公室的。 在办公室里一次进出所花费的时间为out_time 减去 in_time。返回结果表单的顺序无要求。
SUM() 函数对分组的时间累加求和

SELECT event_day AS day,emp_id,SUM(out_time - in_time) AS total_time
FROM Employees
GROUP BY event_day,emp_id 
1
2
3

1407. 排名靠前的旅行者

返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。

COALESCE函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL。

SELECT u.name,IFNULL(SUM(r.distance),0) AS travelled_distance
FROM Users u LEFT JOIN Rides r
ON u.id = r.user_id
GROUP BY u.id
ORDER BY travelled_distance DESC,name
1
2
3
4
5

1393. 股票的资本损益

编写一个SQL查询来报告每支股票的资本损益。股票的资本损益是一次或多次买卖股票后的全部收益或损失。以任意顺序返回结果即可。

SELECT stock_name,SUM(IF(operation = "Buy",-price,price)) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name

==========================================

SELECT stock_name,SUM(CASE WHEN operation = "Buy" THEN -price ELSE price END) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name
1
2
3
4
5
6
7
8
9

1158. 市场分析 I

请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。以 任意顺序 返回结果表。
结果需要查询用户，注册日期，2019年作为卖家的订单总数
使用子查询将2019年的买家用户和其订单总数作为一张表，返回给外连接与用户表连接，查询出结果

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) AS cnt
    FROM Orders
    WHERE YEAR(order_date) = "2019"
    GROUP BY buyer_id
) UserBuy
ON Users.user_id = UserBuy.buyer_id;
1
2
3
4
5
6
7
8
9

1587. 银行账户概要 II

写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.
返回结果表单没有顺序要求.

分析
查询的姓名和余额，余额是事务表中数量的和，由于使用左外连接，IFNULL变换null 的情况，通过账号分组，因为查询的是用户

SELECT Users.name AS NAME,IFNULL(SUM(Transactions.amount),0) AS BALANCE
FROM Users
LEFT JOIN Transactions ON Users.account = Transactions.account
GROUP BY Users.account
HAVING BALANCE > 10000
1
2
3
4
5