LeeCode数据库部分题目汇总

LeeCode数据库部分SQL题目总结

176. 第二高的薪水

描述

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）

IdSalary
1100
2200
3300

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

SecondHighestSalary
200

数据准备

Create table If Not Exists Employee (Id int, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values ('1', '100');
insert into Employee (Id, Salary) values ('2', '200');
insert into Employee (Id, Salary) values ('3', '300');

SQL语句

SELECT MAX(Salary) SecondHighestSalary
FROM Employee
WHERE Salary <
(SELECT MAX(Salary) FROM Employee)

178.分数排名

描述

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

IdScore
13.50
23.65
34.00
43.85
54.00
63.65

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

ScoreRank
4.001
4.001
3.852
3.653
3.653
3.504

数据准备

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');

SQL语句

SELECT
Score,
@rank := @rank + (@prev <> (@prev := Score)) Rank
FROM
Scores,
(SELECT @rank := 0, @prev := -1) init
ORDER BY Score desc

180. 连续出现的数字

描述

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

IdNum
11
21
31
42
51
62
72

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

ConsecutiveNums
1

数据准备

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

SELECT DISTINCT l1.Num 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

181. 超过经理收入的员工

描述

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

IdNameSalaryManagerId
1Joe700003
2Henry800004
3Sam60000null
4Max90000null

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

Employee
Joe

数据准备

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');

SQL语句

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;

182. 查找重复的电子邮箱

描述

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

IdEmail
1a@b.com
2c@d.com
3a@b.com

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

Email
a@b.com

说明：所有电子邮箱都是小写字母。

数据准备

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

-- 方法1：
select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;
-- 方法2
select Email
from Person
group by Email
having count(Email) > 1;
-- 方法3
select
     distinct(P1.Email) 'Email'
from
      Person P1,
      Person P2
where P1.Id <> P2.Id and P1.Email = P2.Email

183. 从不订购的客户

描述

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

Customers 表：

IdName
1Joe
2Henry
3Sam
4Max

Orders 表：

IdCustomerId
13
21

例如给定上述表格，你的查询应返回：

Customers
Henry
Max

数据准备

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');

SQL语句

-- 方法1：
SELECT
	a.NAME 'Customers' 
FROM
	Customers a
	LEFT JOIN Orders b ON a.Id = b.CustomerId 
WHERE
	b.Id IS NULL
-- 方法2：
SELECT NAME
	'Customers' 
FROM
	Customers 
WHERE
	Id NOT IN ( SELECT CustomerId FROM Orders )

184. 部门工资最高的员工

描述

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

IdNameSalaryDepartmentId
1Joe700001
2Henry800002
3Sam600002
4Max900001

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

IdName
1IT
2Sales

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

DepartmentEmployeeSalary
ITMax90000
SalesHenry80000

数据准备

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

SQL语句

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

185.部门工资前三高的所有员工

描述

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

IdNameSalaryDepartmentId
1Joe850001
2Henry800002
3Sam600002
4Max900001
5Janet690001
6Randy850001
7Will700001

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

IdName
1IT
2Sales

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

DepartmentEmployeeSalary
ITMax90000
ITRandy85000
ITJoe85000
ITWill70000
SalesHenry80000
SalesSam60000

解释：

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

数据准备

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '85000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Janet', '69000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('6', 'Randy', '85000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('7', 'Will', '70000', '1');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');

SQL语句

SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE -- 相关子查询，父查询传递一个元祖到子查询，遍历子查询的的数据，如果满足不超过3个人的工资大于传过来的工资，则保留该元祖的数据，否则就过滤掉
3 > (SELECT
COUNT(DISTINCT e2.Salary) -- 对于重复的工资，计数一次，从而保证相同的工资的排名相同
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)

196.删除重复的邮箱

描述

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

IdEmail
1john@example.com
2bob@example.com
3john@example.com

Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

IdEmail
1john@example.com
2bob@example.com

数据准备

Create table If Not Exists Person (Id int,Email varchar(20));
Truncate table Person;
insert into Person values ('1',  'john@example.com');
insert into Person values ('2',  'bob@example.com');
insert into Person values ('3',  'john@example.com');

SQL语句

DELETE p1.* 
FROM
	Person p1,
	Person p2 
WHERE
	p1.Email = p2.Email 
	AND p1.Id > p2.Id

197.上升的温度

描述

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

Id(INT)RecordDate(DATE)Temperature(INT)
12015-01-0110
22015-01-0225
32015-01-0320
42015-01-0430

例如，根据上述给定的 Weather 表格，返回如下 Id:

id
2
4

数据准备

Create table If Not Exists Weather (Id int, Date date, Temperature int);
Truncate table Weather;
insert into Weather (Id, Date, Temperature) values ('1', '2015-01-01', '10');
insert into Weather (Id, Date, Temperature) values ('2', '2015-01-02', '25');
insert into Weather (Id, Date, Temperature) values ('3', '2015-01-03', '20');
insert into Weather (Id, Date, Temperature) values ('4', '2015-01-04', '30');

SQL语句

SELECT
	a.Id 
FROM
	Weather a
	JOIN Weather b ON DATEDIFF(a.RecordDate,b.RecordDate) = 1
WHERE
	a.Temperature > b.Temperature

262.行程与用户

题目描述

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

IdClient_IdDriver_IdCity_IdStatusRequest_at
11101completed2013-10-01
22111cancelled_by_driver2013-10-01
33126cancelled2013-10-01
44136cancelled_by_client2013-10-01
51101completed2013-10-02
62116completed2013-10-02
73126completed2013-10-02
821212completed2013-10-03
931012completed2013-10-03
1041312cancelled_by_driver2013-10-03

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

Users_IdBannedRole
1Noclient
2Yesclient
3Noclient
4Noclient
10Nodriver
11Nodriver
12Nodriver
13Nodriver

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

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

DayCancellation Rate
2013-10-010.33
2013-10-020.00
2013-10-030.50

数据准备

Create table If Not Exists Trips (Id int, Client_Id int, Driver_Id int,
City_Id int, Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'),
Request_at varchar(50));
Create table If Not Exists Users (Users_Id int,
Banned varchar(50), Role ENUM('client', 'driver', 'partner'));
Truncate table Trips;
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status,
Request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
Truncate table Users;
insert into Users (Users_Id, Banned, Role) values ('1', 'No', 'client');
insert into Users (Users_Id, Banned, Role) values ('2', 'Yes', 'client');
insert into Users (Users_Id, Banned, Role) values ('3', 'No', 'client');
insert into Users (Users_Id, Banned, Role) values ('4', 'No', 'client');
insert into Users (Users_Id, Banned, Role) values ('10', 'No', 'driver');
insert into Users (Users_Id, Banned, Role) values ('11', 'No', 'driver');
insert into Users (Users_Id, Banned, Role) values ('12', 'No', 'driver');
insert into Users (Users_Id, Banned, Role) values ('13', 'No', 'driver');

SQL语句

# 方法1：
SELECT
	temp1.Request_at AS DAY,
IF
	(
	cast( ( temp2.cancelled_order / temp1.total_order ) AS DECIMAL ( 3, 2 ) ) IS NULL,
	0.00,
	cast( ( temp2.cancelled_order / temp1.total_order ) AS DECIMAL ( 3, 2 ) ) 
	) AS 'Cancellation Rate' 
FROM
	(
SELECT
	t1.Request_at,
	count( * ) AS total_order 
FROM
	( SELECT * FROM Trips WHERE Request_at >= '2013-10-01' AND Request_at <= '2013-10-03' ) t1
	JOIN ( SELECT * FROM Users WHERE Banned = 'NO' ) t2 ON t1.Client_Id = t2.Users_Id 
GROUP BY
	t1.Request_at 
	) temp1
	LEFT JOIN (
SELECT
	t1.Request_at,
	count( * ) AS cancelled_order 
FROM
	( SELECT * FROM Trips WHERE Request_at >= '2013-10-01' AND Request_at <= '2013-10-03' AND ( STATUS = 'cancelled_by_driver' OR STATUS = 'cancelled_by_client' ) ) t1
	JOIN ( SELECT * FROM Users WHERE Banned = 'NO' ) t2 ON t1.Client_Id = t2.Users_Id 
GROUP BY
	t1.Request_at 
	) temp2 ON temp1.Request_at = temp2.Request_at
	-- ---------------------------------------------------------
	# 方法2：
	SELECT
	temp.request_at DAY,
	round( sum( CASE temp.STATUS WHEN 'completed' THEN 0 ELSE 1 END ) / count( temp.STATUS ), 2 ) 'Cancellation Rate' 
FROM
	( SELECT STATUS, request_at FROM trips t LEFT JOIN users u ON t.client_id = u.users_id WHERE u.banned = 'no' ) temp 
WHERE
	request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	temp.request_at

511.游戏玩家分析I

描述

找出每个玩家第一次登录的日期。Activity表如下：

player_iddevice_idevent_dategames_played
122016**-03-**015
122016-03-026
232017-06-251
312016-03-020
342018**-07-**035

结果Result表如下：

player_idfirst_login
12016-03-01
22017**-06-**25
32016-03-02

数据准备

Create table If Not Exists activity(player_id int,device_id int,event_date date,games_played int);
Truncate table activity;
insert into activity values (1,2,'2016-03-01',5);
insert into activity values (1,2,'2016-03-02',6);
insert into activity values (2,3,'2017-06-25',1);
insert into activity values (3,1,'2016-03-02',0);
insert into activity values (3,4,'2018-07-03',5);

SQL语句

select player_id,min(event_date) first_login from activity group by player_id ;

512. 游戏玩家分析II

描述

显示每个玩家首次登录的设备号(同时显示玩家ID)。

数据准备

见511题

SQL语句

SELECT
	player_id,
	device_id 
FROM
	activity 
WHERE
	( player_id, event_date ) IN ( SELECT player_id, min( event_date ) first_login FROM activity GROUP BY player_id )

534 游戏玩家分析III

描述

编写一个 SQL 查询，同时显示每组玩家、日期以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。

结果为：

player_idevent_dategames_played_so_far
12016-03-015
12016-03-0211
12017-06-251
32016-03-020
32018-07-035

数据准备

见511题

SQL语句

-- 方法一
SELECT
	B.player_id,
	B.event_date,
	SUM( A.games_played ) AS `games_played_so_far` 
FROM
	Activity AS A
	JOIN Activity AS B ON ( A.player_id = B.player_id AND A.event_date <= B.event_date ) 
GROUP BY
	B.player_id,
	B.event_date
-- 方法二
SELECT C.player_id,C.event_date,C.games_played_so_far
FROM (
SELECT 
	A.player_id,
	A.event_date,
@sum_cnt:=
		if(A.player_id = @pre_id AND A.event_date != @pre_date,
			@sum_cnt + A.games_played,
			A.games_played 
		)
		AS `games_played_so_far`,
@pre_id:=A.player_id AS `player_ids`,
@pre_date:=A.event_date AS `event_dates`

FROM 
activity AS A,(SELECT @pre_id:=NULL,@pre_date:=NULL,@sum_cnt:=0) AS B
order BY A.player_id,A.event_date
) AS C

550 游戏玩家分析IV

描述

列出首次登录后，紧接着第二天又登录的人数占总人数的比例。比如511题中的数据，只有玩家1连续两天登录了，而总玩家有3个，所以连着两天登录的用户比例为：1/3 ~0.33

数据准备

见511题

SQL语句

SELECT
	ROUND(
	(
	-- 求第二天连续登陆的用户数
SELECT
	count( DISTINCT player_id ) AS con_cnt 
FROM
	(
SELECT
	a.player_id,
	DATEDIFF( b.event_date, a.event_date ) AS diff 
FROM
	activity a
	JOIN activity b ON ( a.player_id = b.player_id AND a.event_date < b.event_date ) 
	) t1 
WHERE
	diff = 1 
	) / ( SELECT count( DISTINCT player_id ) total_cnt FROM activity ),-- 总用户数
	2 
	) fraction

569 员工薪水中位数

描述

有一张员工表Employees，字段为Id，Name，Salary，其中Id为员工Id，Name为公司名称，Salary为员工工资。如下面数据所示：

IdCompanySalary
1A2341
2A341
3A15
4A15314
5A451
6A513
7B15
8B13
9B1154
10B1345
11B1221
12B234
13C2345
14C2645
15C2645
16C2652
17C65

请编写SQL查询来查找每个公司的薪水中位数。结果如下：

IdCompanySalary
5A451
6A513
12B234
9B1154
14C2645

数据准备

drop  table if exists employees;
Create table employees(Id int,Company varchar(2),salary int);
insert into employees values(1,'A',2341);
insert into employees values(2,'A',341);
insert into employees values(3,'A',15);
insert into employees values(4,'A',15314);
insert into employees values(5,'A',451);
insert into employees values(6,'A',513);
insert into employees values(7,'B',15);
insert into employees values(8,'B',13);
insert into employees values(9,'B',1154);
insert into employees values(10,'B',1345);
insert into employees values(11,'B',1221);
insert into employees values(12,'B',234);
insert into employees values(13,'C',2345);
insert into employees values(14,'C',2645);
insert into employees values(15,'C',2645);
insert into employees values(16,'C',2652);
insert into employees values(17,'C',65);

SQL语句

select
     t1.id,
     t1.company,
     t1.salary
from

(
-- 查询每个公司员工薪水排名
select
     id,
     company,
     salary,
     @num := if( @company =company  ,@num + 1,1) as rank,
     @company := company
from employees a ,(select @num := 0,@company:="") b
order by company,salary) t1 
join
(
-- 查询每个公司有多少个员工
select
     company,
     count(*) as cnt
from
    employees
group by company

) t2 on t1.company= t2.company and t1.rank = (t2.cnt + 1) div 2 -- （员工总数+1）/2 为中位数

公众号『大数据技术与数仓』，回复『资料』领取大数据资料包

posted @ 2020-08-07 23:07  大数据技术与数仓  阅读(183)  评论(0)    收藏  举报