当前位置:   article > 正文

【持续更新】Leetcode SQL题目全解析(附建表sql)_leetcode 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
  1. 时间轴不连续
  • 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
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/IT小白/article/detail/131019
推荐阅读
相关标签
  

闽ICP备14008679号