力扣题目资源来自“熊大的数据分析之路”，写下这系列练习记录是为了自己实践一遍，非商业用途。
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
select FirstName, LastName, City, State
from Person left join AddressId
where Person.PersonId=Address.PersonId
这道题好基础，以Person为主表，进行左链接即可
2. 第二高的薪水
编写一个 SQL 查询，获取Employee表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述Employee表，SQL查询应该返回200作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
我的想法是：在排除掉最高薪资后的数据内，选取其最高薪资，即第二高薪资。所以有了这个代码：
select max(Salary) as SecondHighestSalary
from employee
where salary< (select max(salary) from employee)
另外学习到了一种排序函数的写法：
select min(salary) "SecondHighestSalary"
from(select salary,dense_rank()over(order by salary desc) as rn
from Employee)
where rn=2;
知识点：
※rank() over(partition by A order by B) ：按照A进行分组，分组里面的数据按照B进行排序，over即在什么之上，rank()即跳跃排序；
比如存在两个第一名，接下来就是第三名。
※dense_rank(): 连续排序，如果有两个第一名时，接下来第三个仍然是第二名。
附文章：https://www.jb51.net/article/20631.htm
3. 第N高的薪水
编写一个 SQL 查询，获取Employee表中第n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述Employee表，n = 2 时，应返回第二高的薪水200。如果不存在第n 高的薪水，那么查询应返回null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
解1:
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
/* Write your T-SQL query statement below. */
select
distinct salary
from
(select salary,dense_rank() over (order by salary desc) as rank
from employee) as t
where rank=@N
);
这个解法是先将薪资表进行连续排序（dense_rank函数），然后取题目要求的第N名，最后进行去重。
————————————————
解2：或者可以这样：
Write your MySQL query statement below：
select distinct Salary
from Employee as e1
where N = (select count(distinct e1.Salary)
from Employe e1，e2
where e1.Salary <= e2.Salary )
这个是不用连续排序（dense_rank函数）进行的薪资排序，把两张薪资表进行逐一的比较，把比较后的结果进行计数，比如薪资表有5个不同的数据：1000，500，2000，500，3000.表1内A薪资3000，和表2比较后，表1只有1个小于等于3000的薪资记录，那么count(distinct e1.Salary)则为1，3000这一薪资记录也排名第一;同理薪资500的话，和表2比较后，表1有5个小于等于500的薪资记录，去重后是4个；这个想了好久，枚举数据之后终于懂了 _(:з」∠)_
————————————————
解3：还有一个更简单的，学习到了
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M INT;
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
select ifnull((select distinct Salary from Employee order by Salary desc limit M,1),null) as getNthHighestSalary
);
END
————————————————
知识点：*IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
*LIMIT M OFFSET L
select * from table limit m,n
其中m是指记录开始的index，从源0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录
如果想去薪资第三名的数据，那么N=3,M=2,题中即从第M(2)行起，取1行数据，即取第三名薪资记录。
4. 分数排名
编写一个 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 |
+-------+------+
解1：(还是也连续排序函数）
select score as "Score", dense_rank() over(order by score desc) as "Rank"
from Scores;
还有一种解法2：
(select count(distinct b.Score) from Scores b
where b.Score >= a.Score)asRank
from Scores a
order by a.Score DESC;
也是延续上一道题的两表比较后，计数大于等于的记录个数，作为排序
“ 提取出大于等于a.Score的所有分数集合H，将H去重后的元素个数就是a.Score的排名。select a.Score as Score”---大佬的解释好清楚，沿用了
5. 连续出现的数字
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的Logs表，1是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解：
select distinct a.Num as"ConsecutiveNums"
from Logsa, Logsb, Logsc
where a.Id=b.Id-1 and a.Id=c.Id-2
and a.Num=b.Num and a.Num=c.Num;
解法就是把logs表*3，再把三张表的ID进行连续三次的where子句筛选，这题应该是可以对应连续登陆、连续购买···这类的情况的。
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 |
+----------+
解：
select a.Name as "Employee"
from Employee a,Employee b
where a.ManagerId = b.Id and a.Salary > b.Salary;
这道题经常遇到，解法是两表用ManagerId = Id相连接后，再去比较薪资即可。
7. 查找重复的电子邮箱
编写一个 SQL 查询，查找Person表中所有重复的电子邮箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。
解：select Email"Email"
from Person
group by Email
having count(Email) >1;
简单题，按邮箱分组后，筛选出计数项大于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 |
+-----------+
解：从不订购=订单表无记录，无ID，算是一种排除法吧。
select Name as "Customers"
from Customers
where Id not in (select CustomerId from Orders)
order by Name;
9. 部门工资最高的员工
Employee表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解：
select b.Name"Department",a.Name"Employee",Salary"Salary"
from Employee a,Department b
where DepartmentId = b.Id
and(Salary,DepartmentId) in
(select max(Salary),DepartmentId
from Employee
groupby DepartmentId);
先在员工信息表里按部门分组找出最高工资的员工，再连接部门表，显示要输出的信息。
10. 部门工资前三高的员工
Employee表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
Department表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解：
select d.name"Department",s.name"Employee",s.salary"Salary"
from(select departmentid,name,salary
from
(select departmentid,
name,
salary,
dense_rank()over(partition by departmentid order by salary desc) rn
from employee)
where rn <4) s,department d
where s.departmentid = d.id
思路：每个部门工资前三高的员工，首先用dense_rank()把部门前3高的记录作为表S，再用表s和部门名称表d进行连接，那么每个部门前三高薪资的记录就出来了。如果不支持dense_rank()函数的话，又要用两表比较，计数去重元素个数做排名的方法了。
↑是熊大的举例图
11. 删除重复的电子邮箱
编写一个 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 |
+----+------------------+
解：
delete d1
from Person d1,Person d2
where d1.Email = d2.Email and d1.Id > d2.Id;
知识点：删除语句，但是最好实际操作数据库的时候不要删除哦~
DELETE FROM table_name
WHERE some_column=some_value;
12. 上升的温度
给定一个Weather表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
例如，根据上述给定的Weather表格，返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
解：
select w1.Id as "Id"
from Weather w1,Weather w2
where w1.RecordDate = w2.RecordDate +1
and w1.Temperature > w2.Temperature;
老办法了，日期的连续要用+1来连接，然后在比较量表中气温即可。
不过要注意日期类型的字段，是可以这样计算的。
13. 行程和用户（重点复习，比较复杂）
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 |
+------------+-------------------+
解：
整体思路是这样：
①限定日期2013年10月1日 至2013年10月3日,要按天进行分组并排序 ；
②非禁止用户，即Users表Banned字段为NO；
③取消率=取消次数/行程总数，还要进行小数点两位的保留；另外取消可能是用户，也可能是司机
select t2.Request_at as 'Day', ROUND(if_null(cancel,0)/count(t.id),2) as "Cancellation Rate"
from ( select t.Request_at,count(t.Status) as cancel
from trips as t
inner join users c
on c.Users_Id = t.Client_Id
inner join users d
d.Users_Id = t.Driver_Id
where t.status !='completed'
and t.Request_at between '2013-10-01' and '2013-10-03'
and c.Banned='No' and d.Banned='No') as t2
group by t2.Request_at
order by t2.Request_at
————————————————
好晕_(¦3」∠)_ 大致思路如上，
有一些坑：
1、比如2013/10/2这一天实际上没有取消订单的，不套用if_null的话，取消率算不出来
2、看了一些参考答案，最终我选择用内连接，感觉比较好懂
3、总结下来，要一步步拆解调试，嵌套再嵌套
刷了13道题，继续下面的13道题吧，这次对排序函数和LIMIT有了一些新的认识！
网友评论