[LeetCode解题]--SQL专题

科技2024-05-17 83

1.最高的薪水

176. 第二高的薪水

[ 题目描述 ]

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。 +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。 +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+

[ 解题思路 ]

[ 代码实现 ]

方法1

SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary ;

方法2

SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary

2.第N高的薪水

177. 第N高的薪水

[ 题目描述 ] [ 解题思路 ] [ 代码实现 ]

3.分数排名

178. 分数排名

[ 题目描述 ]

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。 +----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ 例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）： +-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+ 重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

[ 解题思路 ]

1.涉及到排名问题，可以使用窗口函数

2.专用窗口函数rank, dense_rank, row_number有什么区别呢？

见链接窗口函数的应用

[ 代码实现 ]

select score, dense_rank() over(order by Score desc) as Ranking from Scores;

4. 连续出现的数字

180. 连续出现的数字

[ 题目描述 ]

编写一个 SQL 查询，查找所有至少连续出现三次的数字。 +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ 例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。 +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+

[ 解题思路 ]

[ 代码实现 ]

连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。

SELECT * FROM Logs l1, Logs l2, Logs l3 WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND l1.Num = l2.Num AND l2.Num = l3.Num ; 结果 Id Num Id Num Id Num 1 1 2 1 3 1

从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。

SELECT DISTINCT l1.Num AS ConsecutiveNums FROM Logs l1, Logs l2, Logs l3 WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND l1.Num = l2.Num AND l2.Num = l3.Num ;

5.超过经理收入的员工

181. 超过经理收入的员工

[ 题目描述 ]

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。 +----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+ 给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。 +----------+ | Employee | +----------+ | Joe | +----------+

[ 解题思路 ]

SELECT a.*,b.* FROM _5_超过经理收入的员工 AS a, _5_超过经理收入的员工 AS b WHERE a.ManagerId = b.Id

[ 代码实现 ]

SELECT a.Name AS 'Employee' FROM Employee AS a, Employee AS b WHERE a.ManagerId = b.Id AND a.Salary > b.Salary ;

6. 查找重复的电子邮箱

182. 查找重复的电子邮箱

[ 题目描述 ]

[ 解题思路 ]

1.看到“找重复”的关键字眼，首先要用分组函数（group by），再用聚合函数中的计数函数count()给姓名列计数。

2.分组汇总后，生成了一个如下的表。从这个表里选出计数大于1的姓名，就是重复的姓名。

[ 代码实现 ]

方法 1

select Email from Person group by Email having count(Email) > 1;

方法 2

select Email from ( select Email, count(Email) as num from Person group by Email ) as statistic where num > 1 ;

7. 从不订购的客户

183. 从不订购的客户

[ 题目描述 ]

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。 Customers 表： +----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Orders 表： +----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ 例如给定上述表格，你的查询应返回： +-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+

[ 解题思路 ]

模型A-B

[ 代码实现 ]

方法1 【A-B】用户不在订单里=用户表里有但是订单表里无 A-B

select a.Name as Customers from Customers as a left join Orders as b on a.Id=b.CustomerId where b.CustomerId is null;

方法2 【not in】

select customers.name as 'Customers' from customers where customers.id not in ( select customerid from orders );

8. 部门工资最高的员工 -- 分组TOPN1

184. 部门工资最高的员工

[ 题目描述 ]

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+ Department 表包含公司所有部门的信息。 +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+ 编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。 +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+ 解释： Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

[ 解题思路 ]

分组求 TOP-N [ 代码实现 ]

方法1：分组求TOP-N

select dep.Name as Department,Employee,Salary from ( select Name as Employee,salary as Salary,DepartmentId, rank() over (partition by departmentid order by salary desc) as ranking from employee ) as emp, department dep where emp.DepartmentId=dep.Id and ranking=1 select d.Name as Department, e.Name as Employee, Salary from ( select *, dense_rank() over (partition by DepartmentId order by Salary desc) as 'Rank' from Employee ) e inner join Department d on e.DepartmentId = d.Id where e.Rank = 1;

方法2

1.先对 DepartmentId 字段分组查询最大值，得到不同 DepartmentId 下的最大值 SELECT DepartmentId, max( Salary ) FROM Employee GROUP BY DepartmentId 2.再根据 DepartmentId 字段连接 Department 表，根据 Salary 和 DepartmentId 查找 Department.Name 字段 SELECT Department.NAME AS Department, Employee.NAME AS Employee, Salary FROM Employee, Department WHERE Employee.DepartmentId = Department.Id AND ( Employee.DepartmentId, Salary ) IN (SELECT DepartmentId, max( Salary ) FROM Employee GROUP BY DepartmentId )

9. 部门工资前三高的所有员工 -- 分组TOPN2

185. 部门工资前三高的所有员工

[ 题目描述 ]

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department 表包含公司所有部门的信息。 +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+ 编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回： +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ 解释： IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

[ 解题思路 ]

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

参考

https://leetcode-cn.com/problems/department-top-three-salaries/solution/tu-jie-sqlmian-shi-ti-jing-dian-topnwen-ti-by-houz/

[ 代码实现 ]

select DepartmentId,Name,Salary from ( select *, dense_rank() over (partition by DepartmentId order by Salary desc) as ranking from Employee) as a where ranking <= 3; 直接再和department表内联结一下获得部门名字就可以通过了 select d.name AS Department,a.name AS Employee,a.Salaryfrom (select *,dense_rank() over(partition by departmentid order by salary desc) as ranking from Employee ) as a,department as d WHERE a.departmentid = d.id and ranking <=3

10. 删除重复的电子邮箱

196. 删除重复的电子邮箱

[ 题目描述 ]

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。 +----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id 是这个表的主键。例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行: +----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+ 提示：执行 SQL 之后，输出是整个 Person 表。使用 delete 语句。

[ 解题思路 ]

代码

select t1.*, t2.* FROM _10_删除重复的电子邮箱 AS t1, _10_删除重复的电子邮箱 AS t2 WHERE t1.email=t2.email

结果

代码

SELECT t1.*, t2.* FROM _10_删除重复的电子邮箱 AS t1, _10_删除重复的电子邮箱 AS t2 WHERE t1.email=t2.email AND t1.id>t2.id

结果

代码

DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id

[ 代码实现 ]

方法1

方法：使用 DELETE 和 WHERE 子句可以使用以下代码，将此表与它自身在电子邮箱列中连接起来。 MySQL SELECT p1.* FROM Person p1, Person p2 WHERE p1.Email = p2.Email ; 然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件。 MySQL SELECT p1.* FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id ; 因为我们已经得到了要删除的记录，所以我们最终可以将该语句更改为 DELETE。 MySQL DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id

方法2

delete from Person where Id not in ( select need.id from ( select min(Id) as id from Person group by Email ) as need )

11. 上升的温度

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

[ 解题思路 ]

代码

SELECT * from _11_上升的温度 AS a left join _11_上升的温度 b on datediff(a.RecordDate, b.RecordDate) = 1

结果

代码

SELECT * from _11_上升的温度 AS a left join _11_上升的温度 b on datediff(a.RecordDate, b.RecordDate) = 1 where a.Temperature>b.Temperature

结果

[ 代码实现 ]

select a.ID from weather as a left join weather as b on datediff(a.RecordDate, b.RecordDate) = 1 where a.Temperature > b.Temperature;

12. 行程和用户

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

[ 解题思路 ]

1.多表联结，找出非禁止的用户

1.1 client 端非禁止用户行程，其中 user_id = 2 是banned=“Yes” 用户。

select * from trips inner join (select users_id from users where banned = 'no') as client on (trips.Client_Id = client.users_id)

1.2 找出非禁止的用户

SELECT * from trips inner join (select users_id from users where banned = 'no') as client on (trips.Client_Id = client.users_id) inner join (select users_id from users where banned = 'no') as driver on (trips.Driver_Id = driver.users_id)

2.按日期分组

因为要计算的是“每天”的取消率，所以要按日期分组，统计每一天的。

group by trips.request_at

此时会生成一张虚拟表。但查询结果如下

SELECT trips.request_at,sum(case when trips.status = 'completed' then 0 ELSE 1 END) AS cancell_count from trips inner join (select users_id from users where banned = 'no') as client on (trips.Client_Id = client.users_id) inner join (select users_id from users where banned = 'no') as driver on (trips.Driver_Id = driver.users_id) group by trips.request_at

3.计算取消率

1. 被司机或乘客取消的非禁止用户生成的订单数量= sum(case when status = 'completed' then 0 else 1 end)

非禁止用户生成的订单总数=count(status)

2. round()

# 向下取整 3 SELECT ROUND(3.1415926) # 向下取整，并保留2位小数点 3.14 SELECT ROUND(3.1415926,2)

3.限制在日期在 2013-10-01 ~ 2013-10-03

最终sql如下：

SELECT trips.request_at AS Day,round(sum(case when trips.status = 'completed' then 0 ELSE 1 end) / count(trips.status),2) AS `Cancellation Rate` from trips inner join (select users_id from users where banned = 'no') as client on (trips.Client_Id = client.users_id) inner join (select users_id from users where banned = 'no') as driver on (trips.Driver_Id = driver.users_id) WHERE trips.Request_at BETWEEN '2013-10-01' AND '2013-10-03' group by trips.request_at

[ 代码实现 ]

13. 大的国家

595. 大的国家

[ 题目描述 ]

这里有张 World 表 +-----------------+------------+------------+--------------+---------------+ | name | continent | area | population | gdp | +-----------------+------------+------------+--------------+---------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000 | | Albania | Europe | 28748 | 2831741 | 12960000 | | Algeria | Africa | 2381741 | 37100000 | 188681000 | | Andorra | Europe | 468 | 78115 | 3712000 | | Angola | Africa | 1246700 | 20609294 | 100990000 | +-----------------+------------+------------+--------------+---------------+ 如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。编写一个SQL查询，输出表中所有大国家的名称、人口和面积。例如，根据上表，我们应该输出: +--------------+-------------+--------------+ | name | population | area | +--------------+-------------+--------------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +--------------+-------------+--------------+

[ 解题思路 ]

[ 代码实现 ]

方法1

SELECT name, population, area FROM world WHERE area > 3000000 OR population > 25000000 ;

方法2

SELECT name, population, area FROM world WHERE area > 3000000 UNION SELECT name, population, area FROM world WHERE population > 25000000 ;

小结

1.使用 or 会使索引会失效，在数据量较大的时候查找效率较低，通常建议使用 union 代替 or。

2.union在连接查询的两张表的时候，会自动去除重复的数据。 union all 在连接查询的时候，只是简单的将两张表中的数据进行连接，不会去除重复的数据。

14. 超过5名学生的课

596. 超过5名学生的课

[ 题目描述 ]

有一个courses 表，有: student (学生) 和 class (课程)。请列出所有超过或等于5名学生的课。例如，表： +---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+ 应该输出: +---------+ | class | +---------+ | Math | +---------+

[ 解题思路 ]

问题出在此处，如下 Chinese 有6人选了，但是Q是一个学生选了5次，有效学生数应该是2个

insert into _14_超过5名学生的课 VALUES ("Q","Chinese"), ("Q","Chinese"), ("Q","Chinese"), ("Q","Chinese"), ("Q","Chinese"), ("X","Chinese")

[ 代码实现 ]

讲道理的话，如下代码已经实现了 SELECT class from( select class,count(student) as cnt from courses group by class having cnt >=5 )t ; 没过原因分析 1.name会有重复，输入参数会出现2条A，Math的记录比如我选了语文，我又选了语文，我还选一次语文，科目都相同，所以根据课程分组里面有三个我，所以需要distinct student，这样保证只有一个我 select class from( select class,count(DISTINCT student) as cnt from courses group by class having cnt >=5 )t ;

如下，了解一下即可

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

15. 体育馆的人流量

601. 体育馆的人流量

[ 题目描述 ]

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、人流量 (people)。请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。例如，表 stadium： +------+------------+-----------+ | id | visit_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 | visit_date | people | +------+------------+-----------+ | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | +------+------------+-----------+ 提示：每天只有一行记录，日期随着 id 的增加而增加。体育馆并不是每天都开放的，所以记录中的日期可能会出现断层

[ 解题思路 ]

[ 代码实现 ]

1. 如果只是找出全部人流量不少于100的记录不难，难点在于如何查找连续的三天，一个想法是，查 3 张表，让三个结果 id 连续

SELECT a.*,b.*,c.* FROM stadium as a,stadium as b,stadium as c where a.id = b.id-1 and b.id+1 = c.id

2.找出全部人流量不少于100的记录

SELECT a.*,b.*,c.* FROM stadium as a,stadium as b,stadium as c where a.id = b.id-1 and b.id+1 = c.id and a.people>=100 and b.people>=100 and c.people>=100;

但是这样输出会有问题，比如 5,6,7,8 号人流量不少于100，但是只输出了 5,6号，根本原因在于，我们将 a 的 id 设为三个连续值中最小值，所以只返回了每 3 个连续值中最小的一个，同理可想到，我们再将 a 的 id 设为三个连续值中中间值和最大值，可以得到全部的连续 3 个值

SELECT a.*,b.*,c.* FROM stadium as a,stadium as b,stadium as c WHERE ((a.id = b.id-1 and b.id+1 = c.id) or (a.id-1 = b.id and a.id+1 = c.id) or (a.id-1 = c.id and c.id-1 = b.id)) AND ( a.people>=100 and b.people>=100 and c.people>=100) ORDER BY a.id;

但是这样还有个问题，比如 5,6,7,8，6 既是 5,6,7 的中间值也是 6,7,8 的最小值，所以还要去重，也许 id 不按序排列，再排序 id，最终得到答案

SELECT distinct a.* FROM stadium as a,stadium as b,stadium as c WHERE ((a.id = b.id-1 and b.id+1 = c.id) or (a.id-1 = b.id and a.id+1 = c.id) or (a.id-1 = c.id and c.id-1 = b.id)) AND ( a.people>=100 and b.people>=100 and c.people>=100) ORDER BY a.id;

参考

https://leetcode-cn.com/problems/human-traffic-of-stadium/solution/ti-yu-guan-de-ren-liu-liang-by-little_bird/

16 . 换座位

626. 换座位

[ 题目描述 ]

小美是一所中学的信息科技老师，她有一张 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 | +---------+---------+ 注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。

[ 解题思路 ]

第一步：理清换座位的逻辑 1）如果原来座位号是奇数的学生，换座位后，这名学生的座位号变为“座位号+1”。 2）如果原来座位号是偶数的学生，换座位后，这名学生的座位号变为“座位号-1”。第二步：如何判断座位号是奇数，还是偶数 sql求余函数：mod(n,m) ，返回n除以m的余数。比如mod(8,2) 的结果是0。如果n除以2的余数是0，说明n是偶数，否则是奇数。转换为判断奇数，偶数的sql就是： case when mod(座位号, 2) != 0 then '奇数' when mod(座位号, 2) = 0 then '偶数' end 第三步：对查询结果排序因为查询结果里座位号是升序排列的，所以最后还要排序（order by 座位号 asc）。加入select子句，最终sql如下： select (case when mod(座位号, 2) != 0 then 座位号 + 1 when mod(座位号, 2) = 0 then 座位号 - 1 end) as '交换后座位号', 姓名 from 学生表 order by 座位号 asc;

[ 代码实现 ]

查询逻辑和前面一样，但是座位总数是奇数，所以：如果最后一个座位号也是奇数，那么他没有可以交换的座位了，所以最后一个座位号的学生不变。

最后一个座位号，等于表里有多少行，可以用count(*) 计算出来

# 最后一个座位号 select count(*) as counts from seat;

最后一个座位号作为条件判断使用时，可以使用子查询，以便调用。最终sql如下。

1. 是奇数且不是最后一位:id+1 2. 是奇数且是最后一位: id 3. 其他: 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 id2,student from seat,(select count(*) as counts from seat) as b order by id asc;

17. 有趣的电影

620. 有趣的电影

[ 题目描述 ]

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数的影片，结果请按等级 rating 排列。例如，下表 cinema: +---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | Ice song | Fantacy | 8.6 | | 5 | House card| Interesting| 9.1 | +---------+-----------+--------------+-----------+ 对于上面的例子，则正确的输出是为： +---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+

[ 解题思路 ]

[ 代码实现 ]

select * from cinema where mod(id, 2) = 1 and description != 'boring' order by rating DESC ;

18.交换工资 [ 性别反转 ]

627. 交换工资 [ 性别反转 ]

[ 题目描述 ]

给定一个 salary 表，如下所示，有 m = 男性和 f = 女性的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。例如： | 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 |

[ 解题思路 ]

在使用update更新表数据前，最好先将原表备份。 [ 代码实现 ]

update salary set sex = (case sex when 'm' then 'f' else 'm' end);

19.重新格式化部门表 [ 行转列 ]

1179. 重新格式化部门表

[ 题目描述 ]

部门表 Department： +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ (id, month) 是表的联合主键。这个表格有关于每个部门每月收入的信息。月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应每个月的收入（revenue）列。查询结果格式如下面的示例所示： Department 表： +------+---------+-------+ | id | revenue | month | +------+---------+-------+ | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar | +------+---------+-------+ 查询得到的结果表： +------+-------------+-------------+-------------+-----+-------------+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue | +------+-------------+-------------+-------------+-----+-------------+ | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null | +------+-------------+-------------+-------------+-----+-------------+ 注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

[ 解题思路 ]

典型的行转列问题

最关键的是使用group by将id进行分组，然后使用聚合函数sum/max对同一id组的所有行进行检索。

1.将最简单的demo实现

SELECT id, (CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue, (CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue, (CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue FROM _1179_重新格式化部门表;

2.一条记录出现了多次，需要对其进行group by分组处理

那么问题出现了，id为1是，对应的 Feb_Revenue Mar_Revenue 没有被取到值【应该有值的】

3. 用聚合函数sum/max进行检索

4.那么答案呼之欲出

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

回答几个问题

a. 为什么要分组 group by

如果不分组，如1所示：有三个id为1 的数据。

b. 讲一下 group by

https://zhanggf.blog.csdn.net/article/details/108950185

c. 为什么要用聚合函数

1) 当一个单元格中有多个数据时，case when只会提取当中的第一个数据。

CASE WHEN month='Feb' THEN revenue END 当id=1时，它只会提取month对应单元格里的第一个数据，即Jan，它不等于Feb，所以找不到Feb对应的revenue，所以返回NULL。即图2中出现的现象。

2) 那该如何解决单元格内含多个数据的情况呢？

答案就是使用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如SUM()或MAX()，每个聚合函数的输入就是每一个多数据的单元格。

SUM(CASE WHEN month='Feb' THEN revenue END) 当id=1时，它提取的Jan、Feb、Mar，从中找到了符合条件的Feb，并最终返回对应的revenue的值，即7000。

Processed: 0.017, SQL: 8