【持续更新】Leetcode SQL题目全解析（附建表sql）_leetcode sql建表

越前须知（雾）

• 本系列将详解Leetcode的SQL题库（附建表语句），采用DBMS为MySQL。
• 本系列仅提供博主个人解法，均经过MySQL检验，且解法有对应注意点，请放心食用。
• 本系列适合从入门到进阶选手；若希望系统学习SQL进阶知识，请移步博主主页。

题目Q & A

175 组合两个表

• 创表

create table Person 
(personid int,
 firstname varchar(255), 
 lastname varchar(255));
 
create table Address 
(addressid int,
 personid int, 
 city varchar(255), 
 state varchar(255));
 
insert into Person values (1, 'San', 'Zhang');
insert into Person values (2, 'Si',  'Li') ;

insert into Address values ('291', 1, 'New York City', 'New York');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Q：查询Person表中成员的名、姓、城市、州
A：以Person表为主，外连接

select firstname, lastname, city, state
	from Person left outer join Address
    on Person.personid = Address.personid;
1
2
3

181 超过经理收入的员工

• 创表

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', null);
insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', null);
1
2
3
4
5
6
7
8
9
10
11

Q：查询工资超过其对应经理的员工名字
A：不是每个员工都有对应经理，内连接，匹配字段分别为经理id和员工id

select E1.name
	from Employee E1, Employee E2
	where E1.managerid = E2.id  -- 经理也是员工
    and E1.salary > E2.salary;
1
2
3
4

182 查找重复电子邮箱

• 创表

create table Email
(id char(4),
 email varchar(50));
 
truncate table Email;
insert into Email values ('1', 'a@b.com');
insert into Email values ('2', 'c@d.com');
insert into Email values ('3', 'a@b.com');
1
2
3
4
5
6
7
8

Q：查询重复的电子邮箱
A：COUNT（*）>1 / 自连接

• COUNT(*)

select email
	from Email
    group by email
    having count(*) > 1;
1
2
3
4

• 自连接，相当于查询邮箱相同但ID不同的“错填”信息

select distinct E1.email
	from Email E1, Email E2
    where E1.email = E2.email
    and E1.id <> E2.id;
1
2
3
4

183 从不订购的用户

• 创表

create table Customers
(id char(4),
 name varchar(10));
 
create table Orders
(id char(4),
 customerid char(4));

insert into Customers values ('1', 'Joe');
insert into Customers values ('2', 'Henry');
insert into Customers values ('3', 'Sam');
insert into Customers values ('4', 'Max');

insert into Orders values ('1', '3');
insert into Orders values ('2', '1');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Q：查询用户总表中没有订购记录的用户
A：从不订购 = 用户总表 - 订购过用户，NOT EXISTS

select id, name
	from Customers C
    where not exists (select *	
						  from Orders O
						  where C.id = O.customerid);
1
2
3
4
5

197 上升的温度

• 创表

create table Weather
 (id int,
  recorddate date,
  temperature int);
  
insert into Weather values (1, '2015-01-01', 10);
insert into Weather values (2, '2015-01-02', 25);
insert into Weather values (3, '2015-01-03', 20);
insert into Weather values (4, '2015-01-04', 30);
1
2
3
4
5
6
7
8
9

Q：查询与昨天/过去最近一天相比，温度升高的日期
A：对应时间轴连续和时间轴不连续解法

1. 时间轴连续

• EXISTS

select W1.id
	from Weather W1
    where exists (select *
					  from Weather W2
                      where W1.recorddate = W2.recorddate + 1
                      and W1.temperature > W2.temperature);
1
2
3
4
5
6

• 自连接

select W1.id
	from Weather W1, Weather W2
    where W1.recorddate = W2.recorddate + 1
    and W1.temperature > W2.temperature;
1
2
3
4

• DATEDIFF()

select W1.id
	from Weather W1, Weather W2
    where W1.temperature > W2.temperature
    and datediff(W1.recorddate, W2.recorddate) = 1;
1
2
3
4

2. 时间轴不连续

• EXISTS，引入W3.recorddate与W1.recorddate的关系

select W1.id
	from Weather W1
	where exists (select * 
					  from Weather W2
					  where W1.temperature > W2.temperature
                      and W2.recorddate = (select max(W3.recorddate)
											   from Weather W3
                                               where W1.recorddate > W3.recorddate));
1
2
3
4
5
6
7
8

• 自连接

select W1.id
	from Weather W1, Weather W2
    where W1.temperature > W2.temperature
    and W2.recorddate = (select max(W3.recorddate)
							 from Weather W3
                             where W1.recorddate > W3.recorddate);
1
2
3
4
5
6

511 & 512 游戏玩法分析

• 创表

create table Activity
(player_id int,
 device_id int,
 event_date date,
 games_played int,
 primary key (player_id, event_date));
 
insert into Activity values (1, 2, '2016-03-01', 5);
insert into Activity values (1, 2, '2016-05-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);
1
2
3
4
5
6
7
8
9
10
11
12

Q：查询每个用户最早登陆时间
A：MIN（）

select player_id, min(event_date) as first_login
	from Activity
    group by player_id;
1
2
3

Q：查询每个用户最早“打开游戏”的时间
A：需要筛选 games_played 大于0

select player_id, min(event_date) as first_login
	from Activity
    where games_played > 0
    group by player_id;
1
2
3
4

Q：查询每个玩家首次登陆的设备名称
A：子查询 / 视图

• 子查询，筛选 event_date

select player_id, device_id
	from Activity A1
    where event_date = (select min(event_date)
							from Activity A2
                            where A1.player_id = A2.player_id
                            group by A2.player_id);
1
2
3
4
5
6

• 临时表

select A1.player_id, A1.device_id
	from Activity A1 inner join (select player_id, min(event_date) as first_date
									 from Activity
									 group by player_id) A2
    on A1.player_id = A2.player_id
    and A1.event_date = A2.first_date;
1
2
3
4
5
6

577 员工奖金

• 创表

create table Employee
(empId int,
 name varchar(20),
 supervisor int,
 salary int,
 primary key (empId));
 
create table Bonus
(empId int,
 bonus int);
 
insert into Employee values (1, 'John', 3, 1000);
insert into Employee values (2, 'Dan',  3, 2000);
insert into Employee values (3, 'Brad', null, 4000);
insert into Employee values (4, 'Thomas', 3, 4000);

insert into Bonus values (2, 500);
insert into Bonus values (4, 2000);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Q：查询奖金不为 2000 的员工
A：需要包含 Bonus 表中未提及的员工1、3，以 Employee 为主表左连接

select name, bonus
	from Employee E left outer join Bonus B
    on B.empId = E.empId
    where B.bonus < 1000
    or B.bonus is null;   -- NULL无法用运算符比较
1
2
3
4
5

584 查询用户推荐人

• 创表

create table Customer
(id int,
 name varchar(10),
 referee_id int);
 
insert into Customer values (1, 'Will', null);
insert into Customer values (2, 'Jane', null);
insert into Customer values (
1
2
3
4
5
6
7