leetcode刷题 (数据库 MySQL) Part 2

LEETCODE数据库题目刷题（目前只有非会员的题目 😐），除了自己的题解还会整合大家的（10道题一个文档）
目前，非会员题目已经整理完 part 1 链接
LEETCODE数据库题库链接

197.上升的温度

题目：
给定一个 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

SQl架构：

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

leetcode链接

解答：
1.表连接 + datediff

select b.Id as Id
from Weather a
join Weather b
where datediff(b.RecordDate, a.RecordDate)=1 and a.Temperature < b.Temperature

这里直接用 a.RecordDate = b.RecordDate-1 的话，会出现不能跨年相减的问题，所以要老老实实用 datediff 。

2.表连接 + timestampdiff

select w2.Id
from Weather as w1, Weather as w2
where TIMESTAMPDIFF(DAY, w1.RecordDate, w2.RecordDate) = 1 AND w2.Temperature > w1.Temperature

3.变量

SELECT
	pc.id
from(
		select 
			if(@preT < a.Temperature AND DATEDIFF(a.RecordDate,@preD)=1,Id,null) AS id,
			@preT := a.Temperature,
			@preD := a.RecordDate
		from Weather a,
		(select @preT:=null,@preD:=null) AS b
		ORDER BY a.RecordDate
	) AS pc
WHERE pc.id is not null;

日期差

1. datediff
   用法：datediff(date1, date2)
   计算两个日期相差的天数，前减后（不能作用于小时或分钟）。
   d1 和 d2 可以是 DATE 或 DATETIME 类型（可以不同）。如果是 DATE 类型，默认时间部分是“00:00:00”的 DATETIME 值。

select datediff('2015-04-22 06:00:00', '2015-04-28')   # -6
select datediff('2015-04-28 06:00:00', '2015-04-22')   # 6

2. timestampdiff
   用法：timestampdiff(unit, d1, d2)
   计算两个时间相差的 年/月/日/小时/分钟/秒，后减前；
   d1 和 d2 可以是 DATE 或 DATETIME 类型（可以不同）。如果是 DATE 类型，默认时间部分是“00:00:00”的 DATETIME 值。

select timestampdiff(year, '2015-04-22', '2011-04-28')  # -3
select timestampdiff(year, '2011-04-28', '2015-04-22')  # 3
select timestampdiff(day, '2015-04-22 01:00:00', '2015-04-28 00:00:00')  # 5

262.行程和用户

题目：
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

注： 这里乘客和司机在一个表里，题目里的用户包括乘客和司机。

SQL架构：

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