如何才能快速掌握MYSQL？如何熟悉使用SQL以满足日常工作需求？

如果你目前啥也不会，只停留在知道SELECT用于查询的层面的话，又想要快速掌握MYSQL，那么刷题，并且过程中不会什么补什么，就是巩固和提升自己的SQL语言能力最快捷的方法。

LeetCode中有不少题是需要Plus会员才能查看并答题的，所以为避免以后会员过期无法再次查看以及加深自身对题目的理解，进行了MYSQL题目的一次大整理。

LeetCode上题目众多，而本篇筛选出了LetCode上出现频率最高的50道题目，并且按出现频率由高到低整理，每道题实现的具体方法和思路都贴在了代码注释，整理不易，希望大家能够做完这些题目或者看完这篇博客，并从中有所获。

PS：本篇博客主要是使用增删查改中的查询功能。

NO.1 简单 (175. 组合两个表)
NO.2 简单 (176. 第二高的薪水)
NO.3 中等 (184. 部门工资最高的员工)
NO.4 中等 (177. 第N高的薪水)
NO.5 中等 (180. 连续出现的数字)
NO.6 困难 (185. 部门工资前三高的所有员工)
NO.7 简单 (1777. 每家商店的产品价格)
NO.8 中等 (178. 分数排名)
NO.9 简单 (181. 超过经理收入的员工)
NO.10 困难 (262. 行程和用户)
NO.11 困难 (601. 体育馆的人流量)
NO.12 简单 (196. 删除重复的电子邮箱)
NO.13 简单 (1179. 重新格式化部门表)
NO.14 简单 (182. 查找重复的电子邮箱)
NO.15 简单 (197. 上升的温度)
NO.16 中等 (626. 换座位)
NO.17 简单 (620. 有趣的电影)
NO.18 简单 (627. 变更性别)
NO.19 简单 (183. 从不订购的客户)
NO.20 简单 (511. 游戏玩法分析 I)
NO.21 简单 (595. 大的国家)
NO.22 简单 (1873. 计算特殊奖金)
NO.23 困难 (1097. 游戏玩法分析 V)
NO.24 困难 (569. 员工薪水中位数)
NO.25 中等 (1841. League Statistics)
NO.26 困难 (571. 给定数字的频率查询中位数)
NO.27 困难 (618. 学生地理信息报告)
NO.28 简单 (1083. 销售分析 II)
NO.29 中等 (1205. 每月交易II)
NO.30 中等 (1501. 可以放心投资的国家)
NO.31 简单 (1821. 寻找今年具有正收入的客户)
NO.32 中等 (1204. 最后一个能进入电梯的人)
NO.33 简单 (1082. 销售分析 I )
NO.34 中等 (1098. 小众书籍)
NO.35 中等 (1270. 向公司CEO汇报工作的所有人)
NO.36 简单 (597. 好友申请 I：总体通过率)
NO.37 中等 (1193. 每月交易 I)
NO.38 简单 (1280. 学生们参加各科测试的次数)
NO.39 中等 (1715. 苹果和橘子的个数)
NO.40 简单 (1809. 没有广告的剧集)
NO.41 简单 (577. 员工奖金)
NO.42 简单 (603. 连续空余座位)
NO.43 中等 (1112. 每位学生的最高成绩)
NO.44 中等 (1308. 不同性别每日分数总计)
NO.45 中等 (570. 至少有5名直接下属的经理)
NO.46 中等 (580. 统计各专业学生人数)
NO.47 简单 (1407. 排名靠前的旅行者)
NO.48 简单 (1795. 每个产品在不同商店的价格)
NO.49 中等 (550. 游戏玩法分析 IV)
NO.50 困难 (579. 查询员工的累计薪水)
结束语

NO.1 简单 (175. 组合两个表)

https://leetcode-cn.com/problems/combine-two-tables/
在这里插入图片描述

# 因为题目要求无论 person 是否有地址信息，所以不能用join，要用left join
select FirstName, LastName, City, State 
from Person 
left join Address on Person.PersonId=Address.PersonId

NO.2 简单 (176. 第二高的薪水)

https://leetcode-cn.com/problems/second-highest-salary/
在这里插入图片描述

# 将第一高的工资筛选掉，再次用MAX就可以取到第二高的工资了。
select max(Salary) as SecondHighestSalary from Employee
where Salary <>
(select max(Salary) from Employee)

NO.3 中等 (184. 部门工资最高的员工)

https://leetcode-cn.com/problems/department-highest-salary/
在这里插入图片描述

# 先用group by的方法求出部门最高薪资，再接着判断Employee表中哪些数据符合即可，
# 过程中会用到join来连接Employee表和Department表。
select d.Name Department, e.Name Employee, Salary
from  Employee e
join Department d on e.DepartmentId=d.Id
where (DepartmentId, Salary) in
(
    select DepartmentId, MAX(Salary) 
    FROM Employee
    group by DepartmentId
)

NO.4 中等 (177. 第N高的薪水)

https://leetcode-cn.com/problems/nth-highest-salary/
在这里插入图片描述

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare m int;
    set m = N - 1;
    RETURN (
    # 这里主要是考LIMIT的用法：
    /*
    LIMIT 5 等价于 LIMIT 0, 5, 即查询(0+1)到(0+5)的数据，也就是1-5的数据。也就是说这里的0是查询的数据起点的前一位，5是查询的数量。
    LIMIT 2 OFFSET 3 等价于 LIMIT 3 2，即查询(3+1)到(3+2)的数据，也就是4-5的数据。也就是说这里的3是查询的数据起点的前一位，2是查询的数量。
    */
    # 那么要查排名第N的数据,那么就是LIMIT N-1, 1，所以是LIMIT m, 1
    # 最后记得去重
    SELECT IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee 
        ORDER BY Salary DESC
        LIMIT m, 1)
    , NULL)
  );
END

NO.5 中等 (180. 连续出现的数字)

https://leetcode-cn.com/problems/consecutive-numbers/
在这里插入图片描述

# 题目要求连续三次及以上，我们只要求出连续三次的数字有哪些即可。
# 取三个Logs表，用来判断。
SELECT DISTINCT L1.Num AS 'ConsecutiveNums'
FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id-l2.Id=1 AND l2.Id-l3.Id=1 AND l1.Num=l2.Num AND l2.Num=l3.Num

NO.6 困难 (185. 部门工资前三高的所有员工)

https://leetcode-cn.com/problems/department-top-three-salaries/
在这里插入图片描述

/*
# 第一种方法是利用窗口函数和dense_rank(),能够对各个部门的工资进行排序
SELECT Department,Employee,Salary
FROM
(
    SELECT
        d.Name AS Department,
        e.Name AS Employee,
        e.Salary AS Salary,
        DENSE_RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) AS rk 
    FROM
        Employee e
        JOIN Department d
        ON e.DepartmentId = d.Id
) AS t
WHERE rk<=3
*/
# 第二种方法运用到的思想是：部门中如果不超过两个工资比我的工资高，那我肯定是前三名。
# 比方说如果我是第二名，那只有一个工资比我高（如我工资是8000，部门其他人是9000,7000,9000,6000）
SELECT 
    d.Name AS Department,
    e.Name AS Employee,
    e.Salary AS Salary
FROM
    Employee e
    JOIN Department d
    ON e.DepartmentId = d.Id
WHERE
    e.Id IN
    (
        SELECT
            e1.Id
        FROM
            Employee e1
            LEFT JOIN Employee e2
            on e1.DepartmentId = e2.DepartmentId AND e1.Salary < e2.Salary
        GROUP BY e1.Id
        HAVING COUNT(DISTINCT e2.Salary) <= 2
    )
    AND e.DepartmentId in (SELECT Id from Department)
    order by d.Id asc,e.Salary desc

NO.7 简单 (1777. 每家商店的产品价格)

https://leetcode-cn.com/problems/products-price-for-each-store/
在这里插入图片描述

# 同题1795. 每个产品在不同商店的价格，正好相反
# 这里主要是记得要用到聚合函数
# 除了用IF函数，同样的也可以用CASE
SELECT 
    product_id, 
    SUM(IF(store='store1', price, NULL)) store1,
    SUM(IF(store='store2', price, NULL)) store2,
    SUM(IF(store='store3', price, NULL)) store3
FROM
    Products
GROUP BY product_id

NO.8 中等 (178. 分数排名)

https://leetcode-cn.com/problems/rank-scores/
在这里插入图片描述

# 第一种方法：利用DENSE_RANK() 窗口函数
/*
SELECT 
    Score,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS 'Rank'
FROM
    Scores
*/
# 第二种方法：主要思想是有几个分数（去重）大于等于我的分数，那这分数就是排第几。
SELECT 
    a.Score,
    (
        SELECT 
            COUNT(DISTINCT b.Score) 
        FROM 
            Scores b
        WHERE a.Score <= b.Score
    ) AS 'Rank'
FROM 
    Scores a
ORDER BY a.Score DESC

NO.9 简单 (181. 超过经理收入的员工)

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/
在这里插入图片描述

# 自连接，将经理和对应的员工处于同一行，再比较即可。
SELECT e1.Name AS 'Employee' 
FROM Employee e1 JOIN Employee e2 ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary

NO.10 困难 (262. 行程和用户)

https://leetcode-cn.com/problems/trips-and-users/
在这里插入图片描述

# 解题思路：首先要将乘客或者司机是非禁止用户的行程筛选出来；
# 接着按日期分组，统计各个日期总订单数；
# 再判断按Status来统计其中取消的订单数量。
SELECT t.Request_at AS 'Day', 
    ROUND((SUM(IF(t.Status='completed', 0, 1))) /
    COUNT(t.Status), 2) AS `Cancellation Rate`
/*
    ROUND((SUM(IF(t.Status='cancelled_by_driver' or t.Status='cancelled_by_client', 1, 0))) /
    COUNT(t.Status), 2) AS `Cancellation Rate`
*/
FROM Trips t
JOIN Users u1 ON (t.Client_Id=u1.Users_Id AND u1.Banned='No')
JOIN Users u2 ON (t.Driver_Id=u2.Users_Id AND u2.Banned='No')
WHERE t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at

NO.11 困难 (601. 体育馆的人流量)

https://leetcode-cn.com/problems/human-traffic-of-stadium/
在这里插入图片描述

# 窗口函数yyds
# 首先是将人流量大于100的数据筛选出来，然后用窗口函数根据id进行排序（一定要是rank而不是dense_rank）
# 利用id-rank，这样就可以将连续的数据分成同一个组（都拥有同一个order1）
# 接着再利用窗口函数根据order1分组并统计各组数量，大于等于3的数据即为所需数据。
SELECT id, visit_date, people
FROM
(
    SELECT id, visit_date, people, COUNT(*) OVER(PARTITION BY order1) AS order2
    FROM
    (
        SELECT *, (id - rank() OVER(ORDER BY id)) AS order1 FROM
        (
            SELECT * FROM Stadium WHERE people >= 100
        ) AS t1
    ) AS t2
) AS t3
WHERE order2 >= 3

NO.12 简单 (196. 删除重复的电子邮箱)

https://leetcode-cn.com/problems/delete-duplicate-emails/
在这里插入图片描述

# 注意是要用到DELETE操作。
DELETE p1
FROM Person p1, Person p2 
WHERE p1.Email=p2.Email AND p1.Id>p2.Id

NO.13 简单 (1179. 重新格式化部门表)

https://leetcode-cn.com/problems/reformat-department-table/
在这里插入图片描述

# IF和CASE都可以，同时注意用上SUM函数。
/*
select id,
    SUM(if(month="Jan", revenue, NULL)) as "Jan_Revenue",
    SUM(if(month="Feb", revenue, NULL)) as "Feb_Revenue",
    SUM(if(month="Mar", revenue, NULL)) as "Mar_Revenue",
    SUM(if(month="Apr", revenue, NULL)) as "Apr_Revenue",
    SUM(if(month="May", revenue, NULL)) as "May_Revenue",
    SUM(if(month="Jun", revenue, NULL)) as "Jun_Revenue",
    SUM(if(month="Jul", revenue, NULL)) as "Jul_Revenue",
    SUM(if(month="Aug", revenue, NULL)) as "Aug_Revenue",
    SUM(if(month="Sep", revenue, NULL)) as "Sep_Revenue",
    SUM(if(month="Oct", revenue, NULL)) as "Oct_Revenue",
    SUM(if(month="Nov", revenue, NULL)) as "Nov_Revenue",
    SUM(if(month="Dec", revenue, NULL)) as "Dec_Revenue"
FROM Department
group by id
*/
select id,
    SUM(CASE WHEN month="Jan" THEN revenue ELSE NULL END) as "Jan_Revenue",
    SUM(CASE WHEN month="Feb" THEN revenue ELSE NULL END) as "Feb_Revenue",
    SUM(CASE WHEN month="Mar" THEN revenue ELSE NULL END) as "Mar_Revenue",
    SUM(CASE WHEN month="Apr" THEN revenue ELSE NULL END) as "Apr_Revenue",
    SUM(CASE WHEN month="May" THEN revenue ELSE NULL END) as "May_Revenue",
    SUM(CASE WHEN month="Jun" THEN revenue ELSE NULL END) as "Jun_Revenue",
    SUM(CASE WHEN month="Jul" THEN revenue ELSE NULL END) as "Jul_Revenue",
    SUM(CASE WHEN month="Aug" THEN revenue ELSE NULL END) as "Aug_Revenue",
    SUM(CASE WHEN month="Sep" THEN revenue ELSE NULL END) as "Sep_Revenue",
    SUM(CASE WHEN month="Oct" THEN revenue ELSE NULL END) as "Oct_Revenue",
    SUM(CASE WHEN month="Nov" THEN revenue ELSE NULL END) as "Nov_Revenue",
    SUM(CASE WHEN month="Dec" THEN revenue ELSE NULL END) as "Dec_Revenue"
FROM Department
group by id

NO.14 简单 (182. 查找重复的电子邮箱)

https://leetcode-cn.com/problems/duplicate-emails/
在这里插入图片描述

# 相同邮箱但不同Id即为重复数据（记得DISTINCT去重）
SELECT DISTINCT p1.Email
FROM Person p1, Person p2
WHERE p1.Email=p2.Email AND p1.Id<>p2.Id

NO.15 简单 (197. 上升的温度)

https://leetcode-cn.com/problems/rising-temperature/
在这里插入图片描述

# 两表之间相互比较即可
SELECT w1.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate,w2.recordDate)=1 AND w1.Temperature > w2.Temperature

NO.16 中等 (626. 换座位)

https://leetcode-cn.com/problems/exchange-seats/
在这里插入图片描述

# 关键在于如何将id转换，这里我用了^(异或)的方法
SELECT IF((SELECT COUNT(*) FROM seat)=id AND id%2<>0, id, (id+1)^1-1) AS id, student
FROM seat
ORDER BY id

NO.17 简单 (620. 有趣的电影)

https://leetcode-cn.com/problems/not-boring-movies/
在这里插入图片描述

# 没啥，就是WHERE的条件还有用到排序
SELECT *
FROM cinema
WHERE id%2=1 AND description<>'boring'
ORDER BY rating DESC

NO.18 简单 (627. 变更性别)

https://leetcode-cn.com/problems/swap-salary/
在这里插入图片描述

# 只能用UPDATE,然后结合CASE或者IF都可以。
UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

NO.19 简单 (183. 从不订购的客户)

https://leetcode-cn.com/problems/customers-who-never-order/
在这里插入图片描述

# 判断哪些用户ID不在订单表里就行了
SELECT Name Customers FROM Customers
WHERE Id NOT IN (SELECT CustomerId FROM Orders)

NO.20 简单 (511. 游戏玩法分析 I)

https://leetcode-cn.com/problems/game-play-analysis-i/
在这里插入图片描述

# 按玩家ID分组，再取MIN(event_date)即可。
SELECT player_id, MIN(event_date) first_login
FROM Activity
GROUP BY player_id

NO.21 简单 (595. 大的国家)

https://leetcode-cn.com/problems/big-countries/
在这里插入图片描述

# 简单
SELECT name, population, area 
FROM World
WHERE area > 3000000 or population > 25000000

NO.22 简单 (1873. 计算特殊奖金)

https://leetcode-cn.com/problems/calculate-special-bonus/
在这里插入图片描述

# 简单
SELECT employee_id, IF(employee_id%2=1 AND LEFT(name, 1)<>'M', salary, 0) bonus
FROM Employees

NO.23 困难 (1097. 游戏玩法分析 V)

https://leetcode-cn.com/problems/game-play-analysis-v/
在这里插入图片描述

# 先取出所有安装日期，然后求出每个安装日期有几个玩家；
# 其中第二天接着玩的有谁(判断玩的日期和安装日期是否相差一天)，就可以了。
SELECT first_day AS install_dt, 
    COUNT(DISTINCT player_id) AS installs,
    ROUND((SUM(IF(DATEDIFF(event_date, first_day)=1, 1, 0)))/COUNT(DISTINCT player_id), 2) AS Day1_retention
FROM
(
    SELECT player_id, event_date, MIN(event_date) OVER(PARTITION BY player_id) AS first_day
    FROM Activity
) AS t1
GROUP BY first_day

NO.24 困难 (569. 员工薪水中位数)

https://leetcode-cn.com/problems/median-employee-salary/
在这里插入图片描述

# 题目什么东西？？不用任何内置函数，抱歉，窗口函数yyds
# 首先要按公司分组，然后对每个公司的员工工资排序（用ROW_NUMBER而不是RANK），然后通过中位数必定大于一半数同时小于一半数+1，得到了中位数。
SELECT Id, Company, Salary
FROM
(
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY Company ORDER BY Salary) AS rk, 
        COUNT(*)  OVER(PARTITION BY Company) AS counts_cmy
    FROM Employee
) AS t
WHERE rK>=counts_cmy/2 AND rK<=counts_cmy/2+1

NO.25 中等 (1841. League Statistics)

https://leetcode-cn.com/problems/league-statistics/
在这里插入图片描述

# 关键点在于连接的时候可以用or，其余就是相加罢了，不难。
SELECT 
    t.team_name,
    COUNT(*) AS 'matches_played', 
    SUM(
        CASE 
            WHEN (t.team_id=m.home_team_id AND m.home_team_goals>m.away_team_goals) OR
                (t.team_id=m.away_team_id AND m.home_team_goals<m.away_team_goals) THEN 3
            WHEN (t.team_id=m.home_team_id AND m.home_team_goals=m.away_team_goals) OR
                (t.team_id=m.away_team_id AND m.home_team_goals=m.away_team_goals) THEN 1
            WHEN (t.team_id=m.home_team_id AND m.home_team_goals<m.away_team_goals) OR
                (t.team_id=m.away_team_id AND m.home_team_goals>m.away_team_goals) THEN 0
        END
    ) AS 'points', 
    SUM(IF(t.team_id=m.home_team_id, m.home_team_goals, m.away_team_goals)) AS 'goal_for', 
    SUM(IF(t.team_id=m.home_team_id, m.away_team_goals, m.home_team_goals)) AS 'goal_against', 
    SUM(IF(t.team_id=m.home_team_id, m.home_team_goals, m.away_team_goals))-SUM(IF(t.team_id=m.home_team_id, m.away_team_goals, m.home_team_goals)) AS 'goal_diff'
FROM Teams t JOIN Matches m ON t.team_id=m.home_team_id OR t.team_id=m.away_team_id
GROUP BY team_id
ORDER BY points DESC, goal_diff DESC, team_name

NO.26 困难 (571. 给定数字的频率查询中位数)

https://leetcode-cn.com/problems/find-median-given-frequency-of-numbers/
在这里插入图片描述

# 下面两种实现方式思路是一样的
# 首先，我们利用自连接的方法，求出小于等于该数字的数量top，再求出小于该数字的数量bottom。
# 然后因为中位数一定小于等于top，大于等于bottom，所以再筛选一下就得出结果。
# 当总数为偶数时，AVG就派上了用场；单数的话就还是本身。

/*
SELECT AVG(Number) AS median
FROM
(
    SELECT n1.Number, SUM(n2.Frequency) AS top, SUM(n2.Frequency)-n1.Frequency AS bottom
    FROM Numbers n1 JOIN Numbers n2 ON n1.Number >= n2.Number
    GROUP BY n1.Number
) AS t
WHERE TOP>=(SELECT SUM(Frequency) FROM Numbers) / 2 AND bottom<=(SELECT SUM(Frequency) FROM Numbers) / 2
*/
SELECT AVG(Number) as median
FROM
(
    SELECT n1.Number FROM Numbers n1 join Numbers n2 on n2.number >= n1.number
    GROUP BY n1.Number
    HAVING SUM(n2.Frequency) >= (SELECT SUM(Frequency) FROM Numbers) / 2
        AND SUM(n2.Frequency) - AVG(n1.Frequency) <= (SELECT SUM(Frequency) FROM Numbers) / 2
) AS t

NO.27 困难 (618. 学生地理信息报告)

https://leetcode-cn.com/problems/students-report-by-geography/
在这里插入图片描述

# 核心思想是将最终生成表中的对应行数提前生成，方便后续按此来分组。MAX没有什么实际含义，只是可以用来聚合，MIN函数也行，而其他聚合函数如SUM函数的话会进行计数，所以不能用。SELECT 
    MAX(IF(continent='America', name, NULL)) AS 'America',
    MAX(IF(continent='Asia', name, NULL)) AS 'Asia',
    MAX(IF(continent='Europe', name, NULL)) AS 'Europe'
FROM
    (SELECT *, ROW_NUMBER() OVER(PARTITION BY continent ORDER BY name) AS rn FROM student) AS t
GROUP BY rn

NO.28 简单 (1083. 销售分析 II)

https://leetcode-cn.com/problems/sales-analysis-ii/
在这里插入图片描述

# 记得去重
SELECT DISTINCT buyer_id 
FROM Sales
WHERE buyer_id IN(
    SELECT buyer_id
    FROM Sales
    WHERE product_id=(SELECT product_id FROM Product WHERE product_name="S8")
    )
    AND 
    buyer_id 
    NOT in(
    SELECT buyer_id
    FROM Sales
    WHERE product_id=(SELECT product_id FROM Product WHERE product_name="iPhone")
    )

NO.29 中等 (1205. 每月交易II)

https://leetcode-cn.com/problems/monthly-transactions-ii/
在这里插入图片描述

# 首先最最最重要的是要读懂题目，declined不代表退单，不能直接加进结果chargeback_amount，approved也只代表目前是已批准的，可以加进approved_amount，但后面有可能退单，也就是会出现在Chargebacks表中，到时候也需要加进chargeback_amount中。
SELECT 
    month, 
    country, 
    SUM(IF(state='approved', 1, 0)) AS approved_count,
    SUM(IF(state='approved', amount, 0)) AS approved_amount,
    SUM(IF(state='declined', 1, 0)) AS chargeback_count,
    SUM(IF(state='declined', amount, 0)) AS chargeback_amount 
FROM
(
    SELECT id, country, amount, state, DATE_FORMAT(trans_date, '%Y-%m') AS month FROM Transactions
    WHERE state<>'declined'
    UNION
    SELECT c.trans_id, t2.country, t2.amount, 'declined' as state, DATE_FORMAT(c.trans_date, '%Y-%m') AS month
    FROM Chargebacks c JOIN Transactions t2 ON t2.id=c.trans_id
) AS t
GROUP BY month, country

NO.30 中等 (1501. 可以放心投资的国家)

https://leetcode-cn.com/problems/countries-you-can-safely-invest-in/
在这里插入图片描述

# 首先是将Person表中的phone_number形式转换成country_code的格式，接着就是和Country表连接。
# 然后再和Calls表连接，注意连接判断时使用 OR。
# 最后按国家分组，求和即可。
SELECT country
FROM
(
    SELECT id, co.name as country, duration
    FROM Person p JOIN Country co ON LEFT(p.phone_number,3) = co.country_code
    JOIN Calls ca ON id=caller_id or id=callee_id
) t
GROUP BY country
HAVING SUM(duration)/COUNT(duration) > (SELECT SUM(duration)/COUNT(duration) FROM Calls)

NO.31 简单 (1821. 寻找今年具有正收入的客户)

https://leetcode-cn.com/problems/find-customers-with-positive-revenue-this-year/
在这里插入图片描述

# 简单
SELECT customer_id FROM Customers
WHERE year=2021 AND revenue > 0

NO.32 中等 (1204. 最后一个能进入电梯的人)

https://leetcode-cn.com/problems/last-person-to-fit-in-the-bus/
在这里插入图片描述

# 最主要的就是要先确定好思路，按turn倒序排，计算6:(6+5+4+3+2+1)、5:(5+4+3+2+1)、4:(4+3+2+1)、3:(3+2+1)、2:(2+1)、1:(1)，选出总重量小于等于1000的，其中最前面的3:(3+2+1)即为所需结果，输出turn为3的人对应的姓名即可。
SELECT q1.person_name
FROM Queue q1, Queue q2
WHERE q1.turn>=q2.turn
GROUP BY q1.turn
HAVING SUM(q2.weight)<=1000
ORDER BY q1.turn DESC
LIMIT 1

NO.33 简单 (1082. 销售分析 I )

https://leetcode-cn.com/problems/sales-analysis-i/
在这里插入图片描述

# 这道题比较简单，用不到Product表。
# 按seller_id分组，求出每个seller的price总和，最大的输出即可。
SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price)=(SELECT MAX(total) FROM 
    (
    SELECT seller_id, SUM(price) as total
    FROM Sales
    GROUP BY seller_id
    ) t1
)

NO.34 中等 (1098. 小众书籍)

https://leetcode-cn.com/problems/unpopular-books/
在这里插入图片描述

# 解决思路：书的上架时间不在近一个月以及书在这一年内的数量不超过10
SELECT book_id, name FROM Books
WHERE available_from < '2019-05-23' AND book_id not in
(
    SELECT book_id
    FROM
    (
        SELECT book_id, SUM(quantity) as 'counts'
        FROM Orders
        WHERE dispatch_date BETWEEN '2018-06-23' AND '2019-06-23'
        GROUP BY book_id
    ) AS t2
    WHERE counts >= 10
)

NO.35 中等 (1270. 向公司CEO汇报工作的所有人)

https://leetcode-cn.com/problems/all-people-report-to-the-given-manager/
在这里插入图片描述

# CEO的员工,CEO的员工的员工，CEO的员工的员工的员工

SELECT employee_id FROM
    (SELECT employee_id FROM Employees WHERE manager_id IN
        (SELECT employee_id FROM Employees WHERE manager_id IN
            (SELECT employee_id FROM Employees WHERE manager_id=1)
        )        
    ) AS t
WHERE employee_id!=1 # CEO的经理还是CEO，去除

NO.36 简单 (597. 好友申请 I：总体通过率)

https://leetcode-cn.com/problems/friend-requests-i-overall-acceptance-rate/
在这里插入图片描述

# 读题：你只需要统计总的被通过的申请数（不管它们在不在表 FriendRequest 中），并将它除以申请总数，得到通过率
# 注意要用到IFNULL函数
SELECT 
    ROUND(
        IFNULL((SELECT COUNT(*) FROM (SELECT DISTINCT requester_id, accepter_id FROM RequestAccepted) AS t1) /
        (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM FriendRequest) AS t2), 0)
    , 2 ) AS accept_rate

NO.37 中等 (1193. 每月交易 I)

https://leetcode-cn.com/problems/monthly-transactions-i/
在这里插入图片描述

# 先将日期改成年月的形式，然后按时期和国家来分组即可。
SELECT DATE_FORMAT(trans_date,'%Y-%m') as month, 
    country, 
    COUNT(*) AS 'trans_count',  
    COUNT(IF(state='approved', 1, NULL)) AS 'approved_count',
    SUM(amount) AS 'trans_total_amount',
    SUM(IF(state='approved', amount, 0)) AS 'approved_total_amount'
FROM Transactions
GROUP BY month, country

NO.38 简单 (1280. 学生们参加各科测试的次数)

https://leetcode-cn.com/problems/students-and-examinations/
在这里插入图片描述

# 首先是要用cross join笛卡尔积将students表和subjects表合成一个12行的表，接着和Examinations表左连接，求attended_exams时记得用e.subject_name而不是b.subject_name，因为用count函数可以把那些null视为0，即没有上课的人会视为0。
SELECT s.student_id, s.student_name, b.subject_name, COUNT(e.subject_name) AS attended_exams
FROM Students s CROSS JOIN Subjects b
    LEFT JOIN Examinations e ON s.student_id=e.student_id AND b.subject_name=e.subject_name
GROUP BY student_id, subject_name
ORDER BY student_id, subject_name

NO.39 中等 (1715. 苹果和橘子的个数)

https://leetcode-cn.com/problems/count-apples-and-oranges/
在这里插入图片描述

# 简单，注意用上IFNULL函数。
SELECT SUM(b.apple_count+IFNULL(c.apple_count,0)) as apple_count, 
    SUM(b.orange_count+IFNULL(c.orange_count,0)) as orange_count
FROM Boxes b 
LEFT JOIN Chests c ON b.chest_id = c.chest_id

NO.40 简单 (1809. 没有广告的剧集)

https://leetcode-cn.com/problems/ad-free-sessions/
在这里插入图片描述

# 先将两表连接，然后判断广告是否在session中，返回有广告的，最后not in，完事。
SELECT session_id FROM Playback
WHERE session_id NOT IN 
(
    
    SELECT DISTINCT session_id FROM Playback p LEFT JOIN Ads a ON p.customer_id=a.customer_id
    WHERE a.timestamp BETWEEN p.start_time AND p.end_time
)

NO.41 简单 (577. 员工奖金)

https://leetcode-cn.com/problems/employee-bonus/
在这里插入图片描述

# 两表相连接，WHERE判断，完事(注意没有bonus也要输出)
SELECT e.name, b.bonus
FROM Employee e LEFT JOIN Bonus b ON e.empId=b.empId
WHERE b.bonus < 1000 OR ISNULL(b.bonus)

NO.42 简单 (603. 连续空余座位)

https://leetcode-cn.com/problems/consecutive-available-seats/
在这里插入图片描述

# 两张cinema表进行比较，简单
SELECT DISTINCT c1.seat_id
FROM cinema c1, cinema c2
WHERE ABS(c1.seat_id-c2.seat_id)=1 AND c1.free = 1 AND c2.free=1
ORDER BY c1.seat_id

NO.43 中等 (1112. 每位学生的最高成绩)

https://leetcode-cn.com/problems/highest-grade-for-each-student/
在这里插入图片描述

# 窗口函数yyds
/*
# 第一种方法：利用窗口函数，根据成绩和course_id，再利用row_number函数得到学生的各科成绩排名,此时同分但course_id小的排名靠前。

SELECT student_id, course_id, grade
FROM
    (SELECT *, row_number() OVER(partition BY student_id ORDER BY grade desc, course_id) AS rownum
    from enrollments        
    ) AS t
WHERE rownum = 1
ORDER BY student_id
*/
# 第二种方法：先是利用窗口函数得到学生的各科成绩排名，然后挑出排名第一的成绩，再利用MIN函数即可取course_id最小的一门。
SELECT student_id, MIN(course_id) AS course_id, grade
FROM
(
    SELECT student_id, course_id, grade, DENSE_RANK() OVER(PARTITION BY student_id ORDER BY grade DESC) AS rk
    FROM Enrollments
) AS t
WHERE rk = 1
GROUP BY student_id
ORDER BY student_id

NO.44 中等 (1308. 不同性别每日分数总计)

https://leetcode-cn.com/problems/running-total-for-different-genders/
在这里插入图片描述

# 简单的道理，就是要对性别相同且日期在之前（包括今天）的得分相加。
SELECT s1.gender, s1.day, SUM(s2.score_points) AS total
FROM Scores s1 JOIN Scores s2 ON s1.gender = s2.gender AND s1.day >= s2.day
GROUP BY s1.gender, s1.day
ORDER BY s1.gender, s1.day
/*
# 或者是用窗口函数动态相加。
SELECT gender, day, SUM(score_points) OVER (PARTITION BY gender ORDER BY gender, day) AS total
FROM Scores;
*/

NO.45 中等 (570. 至少有5名直接下属的经理)

https://leetcode-cn.com/problems/managers-with-at-least-5-direct-reports/
在这里插入图片描述

# 简单
SELECT Name
FROM Employee
WHERE Id IN
(
    SELECT ManagerId
    FROM Employee
    GROUP BY ManagerId
    HAVING COUNT(ManagerId) >= 5
)

NO.46 中等 (580. 统计各专业学生人数)

https://leetcode-cn.com/problems/count-student-number-in-departments/
在这里插入图片描述

# 审题：（即使没有学生的专业也需列出）
SELECT d.dept_name, COUNT(s.dept_id) AS 'student_number'
FROM student s
RIGHT JOIN department d
ON s.dept_id=d.dept_id
GROUP BY d.dept_id
ORDER BY student_number DESC, dept_name

NO.47 简单 (1407. 排名靠前的旅行者)

https://leetcode-cn.com/problems/top-travellers/
在这里插入图片描述

# 跟题：580. 统计各专业学生人数，不同说毫无关系，只能说完全一致。
SELECT u.name, IFNULL(SUM(r.distance), 0) AS 'travelled_distance'
FROM  Users u LEFT JOIN Rides r ON r.user_id=u.id
GROUP BY u.id
ORDER BY travelled_distance DESC, name

NO.48 简单 (1795. 每个产品在不同商店的价格)

https://leetcode-cn.com/problems/rearrange-products-table/
在这里插入图片描述

# 同题1777. 每家商店的产品价格，正好相反过来。
# 记得使用union就简单
SELECT product_id,
    'store1' AS 'store',
    store1 AS price
FROM
    Products
WHERE store1 is not NULL
UNION ALL
SELECT product_id,
    'store2' AS 'store',
    store2 AS price
FROM
    Products
WHERE store2 is not NULL
UNION ALL
SELECT product_id,
    'store3' AS 'store',
    store3 AS price
FROM
    Products
WHERE store3 is not NULL

NO.49 中等 (550. 游戏玩法分析 IV)

https://leetcode-cn.com/problems/game-play-analysis-iv/
在这里插入图片描述

# 先求出每个用户的安装日期，然后求第二天还玩的人数，然后是统计总共用户人数，相除即可。
SELECT ROUND((part.hascount / total.allcount), 2) fraction
FROM
(
    SELECT COUNT(*) AS hascount
    FROM
    (
        SELECT player_id, MIN(event_date) as first_day
        FROM Activity
        GROUP BY player_id
    ) a1, Activity a2
    WHERE a1.player_id=a2.player_id AND DATEDIFF(a2.event_date, a1.first_day)=1
) AS part,
(SELECT COUNT(DISTINCT player_id) AS allcount FROM Activity) total

NO.50 困难 (579. 查询员工的累计薪水)

https://leetcode-cn.com/problems/find-cumulative-salary-of-an-employee/
在这里插入图片描述

# 先除去每个员工的最近一个月数据
# 接着用两个相同的表（上述得到的表），将id相同，月份在近三个月（月份小，但相差小于3）的数据连接起来
# 最后按id和月份分组，求和即可（记得排序）
/*
SELECT t1.Id,t1.Month, SUM(t2.Salary) Salary
FROM (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t1 JOIN (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t2
ON t1.Id=t2.Id AND t1.Month>=t2.Month AND t1.Month-t2.month<3
GROUP BY t1.Id,t1.Month
ORDER BY t1.Id, t1.Month DESC
*/

# 或者先连接然后再筛掉最大月份
SELECT t1.Id,t1.Month, SUM(t2.Salary) Salary
FROM Employee t1 JOIN Employee t2
ON t1.Id=t2.Id AND t1.Month>=t2.Month AND t1.Month-t2.month<3
WHERE (t2.Id, t2.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id) AND (t1.Id, t1.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id) 
GROUP BY t1.Id,t1.Month
ORDER BY t1.Id, t1.Month DESC

结束语

感谢收看，祝学业和工作进步！

推荐关注的专栏

👨‍👩‍👦‍👦 机器学习：分享机器学习实战项目和常用模型讲解
👨‍👩‍👦‍👦 数据分析：分享数据分析实战项目和常用技能整理

CSDN@报告，今天也有好好学习

张士玉小黑屋

当前位置：首页 » 《关注互联网》 » 正文

如何快速掌握MYSQL？附LeetCode上出现频率最高的50道数据库题目详解_老吴的博客

5 人参与 2022年01月20日 17:06 分类 : 《关注互联网》 评论

目录