力扣刷题SQL篇（三）

1、连续出现的数字

https://leetcode-cn.com/problems/consecutive-numbers/)

表结构：

Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')

要求：

编写一个 SQL 查询，查找所有至少连续出现三次的数字。例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

思路：

• 窗口函数lead() over()，用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL） 。可以理解成窗口向后滑动
• lag() over()用法相同，方向相反。

代码：

SELECT DISTINCT Num AS ConsecutiveNums
FROM
(
    SELECT Num, LEAD(Num,1) OVER() AS num1, LEAD(Num,2) OVER() AS num2
    FROM logs
) AS c 
WHERE c.Num = c.num1
AND c.num1 = c.num2

另解：

讨论区看到的大佬的解法

SELECT DISTINCT a.Num ConsecutiveNums FROM (
    SELECT t.Num,
    @cnt:=IF(@pre=t.Num, @cnt+1, 1) cnt,
    @pre:=t.Num pre
    FROM Logs t, (SELECT @pre:=null,@cnt:=0) b
)a
WHERE a.cnt >= 3

用两个变量pre和cnt，pre为上一次的Num，cnt为pre连续出现的次数。

2、超过经理收入的员工

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/

表结构：

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int)
Truncate table Employee
insert into Employee (Id, Name, Salary, ManagerId) values ('1', 'Joe', '70000', '3')
insert into Employee (Id, Name, Salary, ManagerId) values ('2', 'Henry', '80000', '4')
insert into Employee (Id, Name, Salary, ManagerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', 'None')

要求：

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

思路：

先将每个员工的id、姓名、工资和他的经理的工资取出来，然后再选择工资高于经理工资的员工姓名。

代码：

SELECT x1.Name AS Employee
FROM Employee x1
LEFT JOIN (SELECT Id, Name, Salary FROM Employee) x2
ON x1.ManagerId = x2.Id
WHERE x1. Salary > x2.Salary

3、查找重复的电子邮箱

https://leetcode-cn.com/problems/duplicate-emails/

表结构：

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (Id, Email) values ('1', 'a@b.com')
insert into Person (Id, Email) values ('2', 'c@d.com')
insert into Person (Id, Email) values ('3', 'a@b.com')

要求：

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

思路：

对email计数即可。

代码：

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1

4、从不订购的客户

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

表结构：

Create table If Not Exists Customers (Id int, Name varchar(255))
Create table If Not Exists Orders (Id int, CustomerId int)
Truncate table Customers
insert into Customers (Id, Name) values ('1', 'Joe')
insert into Customers (Id, Name) values ('2', 'Henry')
insert into Customers (Id, Name) values ('3', 'Sam')
insert into Customers (Id, Name) values ('4', 'Max')
Truncate table Orders
insert into Orders (Id, CustomerId) values ('1', '3')
insert into Orders (Id, CustomerId) values ('2', '1')

要求：

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

思路：

从不订购任何东西的客户，其Id不会出现在Orders表中。

代码：

SELECT Name AS Customers
FROM Customers 
WHERE Id NOT IN (SELECT DISTINCT CustomerId FROM Orders)