Leetcode之MySQL练习题

写在前面:

最近的工作涉及到SQL比较多,前一段时间做了牛客的SQL练习题,从今天开始来做一下LeetCode上的SQL练习题。为自己之后的理想职业数据分析师来做一些基础的提高。

题目1:难度:简单

表1: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:

FirstName, LastName, City, State

思路:

题目较简单,使用left join就行

程序:

# Write your MySQL query statement below
SELECT a.FirstName, a.LastName, b.City, b.State
FROM Person AS a
LEFT JOIN Address AS b
ON a.PersonId = b.PersonId

题目2:难度:简单

编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

思路:

使用一个子查询来实现

程序:

# Write your MySQL query statement below
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(a.Salary) FROM Employee AS a)

 第三题:难度:中等

编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

思路:

第一感觉是使用窗口函数,使用dense_rank来寻找排名第N的薪水,后来想可以用常规的思维来处理。之后对比下效率如何。

程序1:窗口函数

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT DISTINCT tmp.salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rk
             FROM 
                employee) AS tmp
        WHERE tmp.rk = N
  );
END

 

 

 程序2:常规思路,需要在函数中定义一个变量(之前没有定义,直接使用数值计算一直报错)

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE A INT;
  SET A = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT salary
      FROM Employee
      GROUP BY salary
      ORDER BY salary DESC
      LIMIT A,1
  );
END

 

 第四题:难度:中等

编写一个 SQL 查询来实现分数排名。

如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

重要提示:对于 MySQL 解决方案,如果要转义用作列名的保留字,可以在关键字之前和之后使用撇号。例如 `Rank`
思路:

使用窗口函数,因为排名对重复数值连续且不区分,因此使用dense_rank() over()函数

程序:

# Write your MySQL query statement below
SELECT Score, dense_rank() over(ORDER BY Score DESC) AS 'Rank'
FROM Scores

 题目5:难度:中等

题目:

编写一个 SQL 查询,查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
思路:

使用3次Logs表的信息,由于需要考虑的是id连续情况下,因此需要进行连续id判断和数值判断。

程序:

# Write your MySQL query statement below
SELECT l1.num AS ConsecutiveNums
FROM Logs AS l1,
Logs AS l2,
Logs AS l3
WHERE l1.num = l2.num
AND l2.num = l3.num
AND l1.id = l2.id - 1
AND l2.id = l3.id - 1
GROUP BY l1.num

新增思路:1

# Write your MySQL query statement below
SELECT
DISTINCT t.num AS ConsecutiveNums
FROM
(SELECT
id,
num,
ROW_NUMBER() OVER(ORDER BY id ASC) AS ranking1,
ROW_NUMBER() OVER(PARTITION BY num ORDER BY id ASC) AS ranking2
FROM
Logs) AS t
GROUP BY
t.num,
t.ranking1 - t.ranking2
HAVING
COUNT(1) >= 3;

新增思路2:

# Write your MySQL query statement below
SELECT
DISTINCT t.num AS ConsecutiveNums
FROM
(SELECT
id,
num,
LEAD(num,1,null) OVER(ORDER BY id) AS ranking1,
LEAD(num,2,null) OVER(ORDER BY id) AS ranking2
FROM
Logs) AS t
WHERE
t.num = t.ranking1
AND
t.ranking1 = t.ranking2
;

题目6:难度:简单

Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

思路:

使用表自关联的方式来解决

程序:

# Write your MySQL query statement below
SELECT e1.Name AS Employee
FROM Employee AS e1
INNER JOIN Employee AS e2
ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary

 题目7:难度:简单

编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。

示例:

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入,你的查询应返回以下结果:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

说明:所有电子邮箱都是小写字母。
思路:

本题思路较简单

程序:

# Write your MySQL query statement below
SELECT 
tmp.Email
FROM
(SELECT Email, COUNT(Email) AS NUM
FROM Person
GROUP BY Email) AS tmp
WHERE tmp.NUM > 1

题目8:难度:简单

题目:

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

Customers 表:

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

Orders 表:

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格,你的查询应返回:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+
思路:

题目较简单

程序:

# Write your MySQL query statement below
SELECT a.Name AS Customers
FROM Customers AS a
WHERE a.Id NOT IN 
(SELECT b.CustomerId FROM Orders AS b)

题目9:难度:简单

题目:

这里有张 World 表

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一个国家的面积超过 300 万平方公里,或者人口超过 2500 万,那么这个国家就是大国家。

编写一个 SQL 查询,输出表中所有大国家的名称、人口和面积。

例如,根据上表,我们应该输出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

思路:

使用UNION 来进行一个或操作

程序:

# Write your MySQL query statement below
SELECT a.name, a.population, a.area
FROM World AS a 
WHERE a.area > 3000000
UNION
SELECT b.name, b.population, b.area
FROM World AS b
WHERE b.population > 25000000

 第10题:

题目:

有一个courses 表 ,有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

应该输出:

+---------+
| class   |
+---------+
| Math    |
+---------+

 

提示:

    学生在每个课中不应被重复计算。
思路:

需要注意的是处理重复的学生,一开始没有考虑出了错。

程序:

# Write your MySQL query statement below
SELECT tmp.class
FROM
(SELECT class, COUNT(DISTINCT student) AS NUM
FROM courses
GROUP BY class) AS tmp
WHERE tmp.NUM >= 5

 题目11:

题目:

某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。

作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。

 

例如,下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

对于上面的例子,则正确的输出是为:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

思路:

正确设置好判断条件就行,这是为数不多的在leetcode上各种陷阱少的题目。

程序:

# Write your MySQL query statement below
SELECT * 
FROM cinema
WHERE description <> 'boring'
AND id % 2 = 1
ORDER BY rating DESC

 题目12:

题目:

给定一个 salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。

注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句。

例如:

| 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    |

思路:

使用条件判别

程序:

# Write your MySQL query statement below
UPDATE salary
SET
sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END

 题目13:

题目:

表 Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息

 

编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。

返回结果 不要求顺序 。

查询结果格式如下例:

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+
2015-01-02 的温度比前一天高(10 -> 25)
2015-01-04 的温度比前一天高(30 -> 20)

思路:

使用DATEDIFF函数来进行日期的辨别,学到了

程序:

# Write your MySQL query statement below
SELECT w1.id
FROM Weather AS w1, Weather AS w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1
AND w1.Temperature > w2.Temperature

 题目14:

题目:

部门表 Department:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份(month)可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。

 

编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。

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

Department 表:
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

查询得到的结果表:
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

注意,结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

思路:

思路比较简单

程序:

# Write your MySQL query statement below
SELECT
id,
SUM(CASE month WHEN 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
SUM(CASE month WHEN 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
SUM(CASE month WHEN 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
SUM(CASE month WHEN 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
SUM(CASE month WHEN 'May' THEN revenue ELSE NULL END) AS May_Revenue,
SUM(CASE month WHEN 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
SUM(CASE month WHEN 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
SUM(CASE month WHEN 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
SUM(CASE month WHEN 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
SUM(CASE month WHEN 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
SUM(CASE month WHEN 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
SUM(CASE month WHEN 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM Department
GROUP BY id
ORDER BY id

 题目15:

题目:

编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id 是这个表的主键。

例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

 

提示:

    执行 SQL 之后,输出是整个 Person 表。
    使用 delete 语句。

思路:

使用一个临时表来构建所有的最小ID,然后删除掉不在这里面的ID

程序:

DELETE FROM Person
WHERE
id
NOT IN
(SELECT tmp.ID FROM(
    SELECT Email,MIN(id) AS ID 
    FROM Person
    GROUP BY Email
    ORDER BY Email
) AS tmp
)

还可以使用窗口函数来做:

# Write your MySQL query statement below
DELETE
FROM
Person
WHERE 
Id 
IN
(SELECT
t.Id
FROM
(SELECT
Id,
Email,
ROW_NUMBER() OVER(PARTITION BY Email ORDER BY Id) AS ranking
FROM
Person) AS t
WHERE
t.ranking > 1) 

 

题目16:难度:中等

题目:

小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢?

 

示例:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

假如数据输入的是上表,则输出结果如下:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

注意:

如果学生人数是奇数,则不需要改变最后一个同学的座位。

思路:

这道题涉及到的东西比较多,需要CASE判断,需要判断边界值,需要找到变换的规律

程序:

# Write your MySQL query statement below
SELECT
(CASE 
WHEN id % 2 = 0 THEN id - 1 
WHEN id % 2 = 1 AND id <> (SELECT MAX(a.id) FROM seat AS a) THEN id + 1
ELSE id
END) AS id,
student
FROM seat
ORDER BY id

题目17:难度:中等

Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解释:

Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。

思路:

第一感觉是使用窗口函数,使用dense_rank并且分区可以将最大值都显示出来

程序:

# Write your MySQL query statement below
SELECT a.Name AS Department, b.Name AS Employee, b.Salary AS Salary
FROM Department AS a INNER JOIN
(SELECT *, dense_rank() over(PARTITION BY DepartmentId ORDER BY Salary DESC) AS rk
FROM Employee) AS b
ON a.Id = b.DepartmentId
WHERE b.rk = 1

 题目18:难度:困难

题目:

Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId 。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

解释:

IT 部门中,Max 获得了最高的工资,Randy 和 Joe 都拿到了第二高的工资,Will 的工资排第三。销售部门(Sales)只有两名员工,Henry 的工资最高,Sam 的工资排第二。

思路:

第一感觉是使用窗口函数来做。

程序:

# Write your MySQL query statement below
SELECT 
tmp.Department,
tmp.Employee,
tmp.Salary
FROM
(SELECT
a.Name AS Department, 
b.Name AS Employee, 
b.Salary AS Salary
FROM
Department AS a INNER JOIN
(SELECT *, dense_rank() over(PARTITION BY DepartmentId ORDER BY Salary DESC) AS rk
FROM Employee) AS b
ON a.Id = b.DepartmentId
WHERE b.rk <= 3
ORDER BY Department) AS tmp

 题目:19 难度:困难

题目:

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id,Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型,枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03|
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止,Role 则是一个表示(‘client’, ‘driver’, ‘partner’)的枚举类型。

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表,你的 SQL 语句应返回如下结果,取消率(Cancellation Rate)保留两位小数。

取消率的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

致谢:
非常感谢 @cak1erlizhou 详细的提供了这道题和相应的测试用例。
思路:

当看到两个表的主键无法关联后,需要使用外键来进行关联。在进行关联的时候,Trips表有两个外键,一个是Client_Id一个是Driver_Id,一个应该是用来定义出租车,另一个应该是用来定义司机。如果Client_id和Driver_id一一对应,则可以使用其中一个来与User表进行关联。但是观察数据发现,Client_id和User_id不一一对应,存在一对多的关系,也就是说用关联一次的话会出现数据增多的情况。因此需要进行二次关联,来保证数据的准确度。这道题不是很复杂,但是需要考虑的东西比较多,仔细想想可以解决。

程序:

# Write your MySQL query statement below
SELECT 
t.request_at AS 'Day',
ROUND(SUM(CASE WHEN t.status = 'completed' THEN 0 ELSE 1 END) / COUNT(t.status),2) AS 'Cancellation Rate'
FROM Trips AS t
INNER JOIN Users AS u1 ON t.client_id = u1.users_id 
INNER JOIN Users AS u2 ON t.driver_id = u2.users_id 
WHERE u1.banned = 'No'
AND u2.banned = 'No'
AND t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at

 题目20:难度:困难

题目:

表:Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录,日期随着 id 的增加而增加

 

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列的结果表。

查询结果格式如下所示。

Stadium table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

Result table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续,并且每行都有 >= 100 的人数记录。
请注意,即使第 7 行和第 8 行的 visit_date 不是连续的,输出也应当包含第 8 行,因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行,因为至少需要三条 id 连续的记录。
思路:

这道题想了很久没有很好的思路,学习了下官方的解答,很详细,大家有兴趣可以去看看。

程序:

# Write your MySQL query statement below
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
	  (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)  -- t1, t2, t3
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id
;

瘦下身:

# Write your MySQL query statement below
SELECT
DISTINCT t1.*
FROM
Stadium AS t1,
Stadium AS t2,
Stadium AS t3
WHERE
t1.people >= 100
AND
t2.people >= 100
AND
t3.people >= 100
AND
(
(t1.id - t2.id = 1 AND t2.id - t3.id = 1)  
OR
(t2.id - t1.id = 1 AND t1.id - t3.id = 1) 
OR
(t3.id - t2.id = 1 AND t2.id - t1.id = 1)
)
ORDER BY
t1.id

 对于其中的三种策略而言,其实有6个,这三个可以代表全部,有重复。

思路很高明啊,学习了。

新的思路,对于这类连续多少这样的问题,使用lead()和lag()函数来发现一些规律就可以做了,

在这道题中得不断调试才找到规律,下面放代码:

# Write your MySQL query statement below
SELECT
t.id,
t.visit_date,
t.people
FROM
(SELECT
id,
visit_date,
people,
LEAD(id,1,NULL) OVER(ORDER BY id) AS ranking1,
LEAD(id,2,NULL) OVER(ORDER BY id) AS ranking2,
LAG(id,1,NULL) OVER(ORDER BY id) AS ranking3,
LAG(id,2,NULL) OVER(ORDER BY id) AS ranking4
FROM
Stadium
WHERE
people >= 100) AS t
WHERE
(t.ranking2 - t.id = 2
AND 
t.ranking2 - t.ranking1 = 1)
OR
(t.id - t.ranking3 = 1
AND
t.ranking3 - t.ranking4 = 1)
OR
(t.ranking1 - t.ranking3 = 2) 
GROUP BY
t.id,
t.visit_date,
t.people
;

 

到这里Leetcode上可以免费做的SQL练习题就做完了,不可不说leetcode还是比牛客要难一个档次啊。

不过还是要在实际的工作中来实际的编程,在实际的工作中会遇到奇奇怪怪的情况和要求,在这种情况下编程没有参照,只能自己来编,对自己的编程能力会有很大的提升。

怀挺!一个月之前SQL还不是很熟练,现在可以应付一些题目和场景了,还需要继续啊。。。

与读者共勉,不负有心人。

 

-----------------------------------------------------------------

还是得加油!

posted on 2020-11-14 22:42  桌子哥  阅读(459)  评论(0编辑  收藏  举报