力扣刷题SQL篇（五）

最新推荐文章于 2024-05-22 00:25:55 发布

冰淇淋lining

最新推荐文章于 2024-05-22 00:25:55 发布

阅读量370

点赞数 1

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/qq_41023125/article/details/108382576

版权

SQL 专栏收录该内容

5 篇文章

订阅专栏

1、行程和用户

https://leetcode-cn.com/problems/trips-and-users/

表结构

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

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

要求

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

思路

这里有个思路转换——取消订单→订单未完成，否则从完成角度来看较复杂。

代码

SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

2、大的国家

https://leetcode-cn.com/problems/big-countries/

表结构

Create table If Not Exists World (name varchar(255), continent varchar(255), area int, population int, gdp int)
Truncate table World
insert into World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', '652230', '25500100', '20343000000')
insert into World (name, continent, area, population, gdp) values ('Albania', 'Europe', '28748', '2831741', '12960000000')
insert into World (name, continent, area, population, gdp) values ('Algeria', 'Africa', '2381741', '37100000', '188681000000')
insert into World (name, continent, area, population, gdp) values ('Andorra', 'Europe', '468', '78115', '3712000000')
insert into World (name, continent, area, population, gdp) values ('Angola', 'Africa', '1246700', '20609294', '100990000000')

如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。

要求

编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

思路

两个条件限制即可

代码

SELECT name, population, area
FROM World
WHERE area >= 3000000
OR population >= 25000000

其他解法

注意到上述代码运行结果只击败了15%的用户，讨论区发现一种新的解法

SELECT name, population, area
FROM World
WHERE area >= 3000000
UNION
SELECT name,population,area
FROM World
WHERE population >= 25000000

稍微快了一些，击败了22.5%的用户。

3、超过5名学生的课

https://leetcode-cn.com/problems/classes-more-than-5-students/

表结构

Create table If Not Exists courses (student varchar(255), class varchar(255))
Truncate table courses
insert into courses (student, class) values ('A', 'Math')
insert into courses (student, class) values ('B', 'English')
insert into courses (student, class) values ('C', 'Math')
insert into courses (student, class) values ('D', 'Biology')
insert into courses (student, class) values ('E', 'Math')
insert into courses (student, class) values ('F', 'Computer')
insert into courses (student, class) values ('G', 'Math')
insert into courses (student, class) values ('H', 'Math')
insert into courses (student, class) values ('I', 'Math')

有一个courses 表，有: student (学生) 和 class (课程)。

要求

请列出所有超过或等于5名学生的课。
Note:
学生在每个课中不应被重复计算。

思路

分组后限制条件，用having。学生不可重复计算，用DISTINCT去重。

代码

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5

4、体育馆的人流量

https://leetcode-cn.com/problems/human-traffic-of-stadium/

表结构

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

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、人流量 (people)。

要求

请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

思路

这题没想到头绪，看的讨论区bryce-28大佬的解法。
大佬原话：

1、首先过滤出people>100的字段
2、开窗，用id减去rank排名，并根据id进行排序。若是连续的那么，差值一定是相同的
3、where过滤出条数>=3的完成解题
第二条没有看懂，如果有理解的欢迎讨论呀~

代码

with t1 as (
select
    id,
    visit_date,
    people,
#求出差值，因为id一定不会相同，所以使用最熟悉的rank就好
    id-rank() over(order by id) rk
from stadium
where people >= 100
)
select
    id,
    visit_date,
    people
from t1
#where条件过滤出条数大于3的
where rk in (
select rk from t1 group by rk having count(1) >= 3);

5、有趣的电影

https://leetcode-cn.com/problems/not-boring-movies/

表结构

Create table If Not Exists cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1))
Truncate table cinema
insert into cinema (id, movie, description, rating) values ('1', 'War', 'great 3D', '8.9')
insert into cinema (id, movie, description, rating) values ('2', 'Science', 'fiction', '8.5')
insert into cinema (id, movie, description, rating) values ('3', 'irish', 'boring', '6.2')
insert into cinema (id, movie, description, rating) values ('4', 'Ice song', 'Fantacy', '8.6')
insert into cinema (id, movie, description, rating) values ('5', 'House card', 'Interesting', '9.1')

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

要求

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数的影片，结果请按等级 rating 排列。

思路

略。

代码

SELECT id,movie,description,rating
FROM cinema
WHERE description != 'boring'
AND id mod 2 = 1
ORDER BY rating DESC

mod也可改为%，或者mod(id,2)也可。

6、换座位

https://leetcode-cn.com/problems/exchange-seats/

表结构

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的

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

要求

小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

思路

奇数变为id+1，偶数变为id-1。

代码

SELECT 
	(CASE WHEN MOD(id,2) != 0 AND counts != id THEN id + 1
		WHEN MOD(id,2) != 0 AND counts = id THEN id
	ELSE id - 1
	END) AS id,
student
FROM seat,
(SELECT COUNT(*) AS counts 
FROM seats) AS seat_counts
ORDER BY id ASC;

其他方法

窗口函数

SELECT id, 
	IF(MOD(id,2) = 1, LEAD(student, 1, student) OVER(), LAG(student, 1) OVER()) AS student
FROM seat;

7、交换工资

https://leetcode-cn.com/problems/swap-salary/

表结构

给定一个 salary 表，如下所示，有 m = 男性和 f = 女性的值。

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

要求

交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

思路

动态转换，用CASE WHEN。

代码

UPDATE salary
SET sex = CASE sex WHEN 'm' THEN 'f'
ELSE 'm' END

其他解法

（1）IF

UPDATE salary
SET sex = IF(sex = 'm', 'f', 'm')

（2）REPLACE

UPDATE salary SET sex = REPLACE('mf', sex, "") WHERE sex <> ""

8、重新格式化部门表

https://leetcode-cn.com/problems/reformat-department-table/

表结构

Create table If Not Exists Department (id int, revenue int, month varchar(5))
Truncate table Department
insert into Department (id, revenue, month) values ('1', '8000', 'Jan')
insert into Department (id, revenue, month) values ('2', '9000', 'Jan')
insert into Department (id, revenue, month) values ('3', '10000', 'Feb')
insert into Department (id, revenue, month) values ('1', '7000', 'Feb')
insert into Department (id, revenue, month) values ('1', '6000', 'Mar')

要求

(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 [“Jan”,“Feb”,“Mar”,“Apr”,“May”,“Jun”,“Jul”,“Aug”,“Sep”,“Oct”,“Nov”,“Dec”]。
编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应每个月的收入（revenue）列。
注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

思路

略。

代码

SELECT id,
    SUM(CASE WHEN `month` = 'Jan' THEN revenue END) Jan_Revenue,
    SUM(CASE WHEN `month` = 'Feb' THEN revenue END) Feb_Revenue,
    SUM(CASE WHEN `month` = 'Mar' THEN revenue END) Mar_Revenue,
    SUM(CASE WHEN `month` = 'Apr' THEN revenue END) Apr_Revenue,
    SUM(CASE WHEN `month` = 'May' THEN revenue END) May_Revenue,
    SUM(CASE WHEN `month` = 'Jun' THEN revenue END) Jun_Revenue,
    SUM(CASE WHEN `month` = 'Jul' THEN revenue END) Jul_Revenue,
    SUM(CASE WHEN `month` = 'Aug' THEN revenue END) Aug_Revenue,
    SUM(CASE WHEN `month` = 'Sep' THEN revenue END) Sep_Revenue,
    SUM(CASE WHEN `month` = 'Oct' THEN revenue END) Oct_Revenue,
    SUM(CASE WHEN `month` = 'Nov' THEN revenue END) Nov_Revenue,
    SUM(CASE WHEN `month` = 'Dec' THEN revenue END) Dec_Revenue
FROM Department GROUP BY id;