LEETCODE--DATABASE

最近在LEETCODE刷了一些数据库的题目，总结一些比较有趣的题目。
我都是选择MYSQL来解答的。

题目为：体育馆的人流量
例如，表 stadium：

id	date	people
1	2017-01-01	10
2	2017-01-02	109
3	2017-01-03	150
4	2017-01-04	99
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

对于上面的示例数据，输出为：

id	date	people
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

Note:
每天只有一行记录，日期随着 id 的增加而增加。

SQL脚本为：

Create table If Not Exists stadium (id int, date DATE NULL, people int)
Truncate table stadium
insert into stadium (id, date, people) values ('1', '2017-01-01', '10')
insert into stadium (id, date, people) values ('2', '2017-01-02', '109')
insert into stadium (id, date, people) values ('3', '2017-01-03', '150')
insert into stadium (id, date, people) values ('4', '2017-01-04', '99')
insert into stadium (id, date, people) values ('5', '2017-01-05', '145')
insert into stadium (id, date, people) values ('6', '2017-01-06', '1455')
insert into stadium (id, date, people) values ('7', '2017-01-07', '199')
insert into stadium (id, date, people) values ('8', '2017-01-08', '188')

思路：
根据提示得到，找出连续ID的数据即可，利用伪列(rownum）与id相减可以得出同组连续的记录

我的答案为：

select a1.* from stadium a1,(
    select ss.id,count(ss.did) as num,ss.did,max(id) as mid,min(id) as mmid from (
 select s.*,(s.id - s.rn) as did from (
 SELECT t.*,@rownum:=@rownum+1 as rn from stadium t,(select @rownum:=0) r where t.people >= 100 ) s ) ss
 group by ss.did
 having count(ss.did)>=3 ) a2
 where a1.id<=a2.mid and a1.id>=a2.mmid

虽然通过了测试，但是性能极低，我稍微修改一下

 select a1.* from stadium a1 where EXISTS (
  select a2.* from (
    select ss.id,count(ss.did) as num,ss.did,max(id) as mid,min(id) as mmid from (
 select s.*,(s.id - s.rn) as did from (
    SELECT t.*,@rownum:=@rownum+1 as rn from stadium t,(select @rownum:=0) r where t.people >= 100 ) s ) ss
  group by ss.did
  having count(ss.did)>=3 ) a2
  where a1.id<=a2.mid and a1.id>=a2.mmid )

性能提高了不少，但是仍然和排在前面的高手相差甚远

题目为：行程和用户

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 |
+————+——————-+

题目脚本

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

我的解答

select e.Request_at as Day,Round(e.n/e.num,2) as 'Cancellation Rate'  from (
select count(1) as num ,t.Request_at,(select count(1) as num from 
 Trips t1 join Users u2 on t1.Client_Id  = u2.Users_Id and u2.Banned  ='No'
    join Users u3 on t1.Driver_Id  = u3.Users_Id and u3.Banned  ='No'
    where t1.Request_at =t.Request_at and t1.Status  in ( 'cancelled_by_driver' , 'cancelled_by_client')

) n from Trips t join Users u on t.Client_Id  = u.Users_Id and u.Banned  ='No'
    join Users u1 on t.Driver_Id  = u1.Users_Id and u1.Banned  ='No' 
  where t.Request_at in ('2013-10-01','2013-10-02','2013-10-03')
 group by t.Request_at) e

这次我的排名比较靠前。。

题目： 换座位
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

假如数据输入的是上表，则输出结果如下：

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

题目脚本

Create table If Not Exists seat(id int, student varchar(255))
Truncate table seat
insert into seat (id, student) values ('1', 'Abbot')
insert into seat (id, student) values ('2', 'Doris')
insert into seat (id, student) values ('3', 'Emerson')
insert into seat (id, student) values ('4', 'Green')
insert into seat (id, student) values ('5', 'Jeames')

我的解答

  select m.id,m.student  from (
select case when t.id1  is not null then t.student1 else t.student  end as student ,t.id  from (
select s.id as id,s.student ,e.id id1,e.student  student1 from    (select * from seat   where id%2!=0) s left join seat  e
    on s.id = e.id-1 ) t
union all 
select case when t.id1 is not null then t.student1 else t.student  end as student ,t.id  from (
select s.id as id,s.student ,e.id id1,e.student  student1 from    (select * from seat   where id%2=0) s left join seat  e
    on s.id = e.id+1 ) t
) m order by m.id

题目：交换工资
给定一个 salary表，如下所示，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。

例如:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

运行你所编写的查询语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

题目脚本

create table if not exists salary(id int, name varchar(100), sex char(1), salary int)
Truncate table salary
insert into salary (id, name, sex, salary) values ('1', 'A', 'm', '2500')
insert into salary (id, name, sex, salary) values ('2', 'B', 'f', '1500')
insert into salary (id, name, sex, salary) values ('3', 'C', 'm', '5500')
insert into salary (id, name, sex, salary) values ('4', 'D', 'f', '500')

我的解答

update salary
   set sex = 
     CASE sex
   WHEN 'f' THEN 'm' 
     WHEN 'm' THEN 'f' 
ELSE null END ;

这道题难度属于简单，update搭配case when 想不到能如此巧妙地解决了这道题目。