[SQL] MySQL查询_LeetCode

最新推荐文章于 2024-05-23 18:05:50 发布
最新推荐文章于 2024-05-23 18:05:50 发布
阅读量778 收藏
点赞数 2
分类专栏： LeetCode 文章标签： SQL LeetCode
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Treasure99/article/details/85039768
版权

目录

一、第N高的薪水【中】

二、分数排名（实现dense_rank()排名函数）【中】

延伸：实现row_number()排名函数

延伸：实现rank()排名函数

三、部门工资前三高的员工【难】

四、体育馆的人流量【难】

五、行程和用户【难】

六、大的国家【易】

七、超过5名学生的课【易】

八、交换工资【易】

九、换座位【中】

一、第N高的薪水【中】

获取 Employee 表中第 n 高的薪水（Salary）。

Id    Salary
1100
2200
3300

如 n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

getNthHighestSalary(2)
200

查询如下：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N - 1;
  RETURN (
  SELECT DISTINCT IFNULL(Salary, NULL) getNthHighestSalary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT N,1
  );
END

二、分数排名（实现dense_rank()排名函数）【中】

如果两个分数相同，则两个分数排名相同。

平分后的下一个名次应该是下一个连续的整数值。也就是名次之间不应该有“间隔”。

Id    Score
13.50
23.65
34.00
43.85
54.00
63.65

例如，根据上述给定的 Scores 表，查询应该返回（按分数从高到低排列）：

ScoreRank 
4.001
4.001
3.85

2

3.653
3.653
3.504

MySQL Server查询：（MySQL不适用dense_rank函数）

SELECT Score, (DENSE_RANK() OVER (ORDER BY Score DESC)) Rank
    FROM Scores;

MySQL查询：方法一：

SELECT s1.Score, COUNT(DISTINCT s2.Score) Rank
FROM Scores s1, Scores s2
WHERE s2.Score >= s1.Score
GROUP BY s1.Id, s1.Score
ORDER BY s1.Score DESC;

方法二：

SELECT a.Score, 
CONVERT((CASE
WHEN @score = a.Score THEN @rank 
WHEN @score := a.Score THEN @rank := @rank+1
WHEN @score = 0 THEN @rank := @rank+1
END), SIGNED) AS 'Rank' 
FROM (SELECT Score FROM Scores ORDER BY Score DESC) a, 
    (SELECT @score := NULL, @rank := 0) b;

说明：

  • 设定变量@score, @rank，并分别初始赋值NULL, 0，赋值为" := " ；

  • 指定排序列的值不变时，@rank的值不变；指定排序列的值变化时，@rank的值自增1；

  • 字符串数字转换：CAST('123' AS SIGNED) 或 CONVERT('123', SIGNED) 或 '123'+0【引申数字转字符串 CONCAT() 】；

  • 如果给定表分数为[0.0]，则返回[0.0, 1]，而不是[0.0, NULL]；

  • 如果MySQL报错“ERROR 1064 (42000): You have an error in your SQL syntax;”，把“AS Rank”改为“AS 'Rank' "可解决。

延伸：实现row_number()排名函数

查询返回：

ScoreRank 
4.001
4.002
3.85

3

3.654
3.655
3.506

MySQL查询：

SELECT a.Score, @rank := @rank+1 AS 'Rank'
FROM (SELECT Score FROM Scores ORDER BY Score DESC) a, 
    (SELECT @rank := 0) b;

说明：

  • 设置变量@rank初始值为0，并逐行累加。

延伸：实现rank()排名函数

查询返回：

ScoreRank 
4.001
4.001
3.85

3

3.654
3.654
3.506

MySQL查询：

SELECT s.Score, s.Rank FROM(
SELECT a.Score, 
    IF(@score = a.Score, @rank, @rank := @inRank) AS 'Rank', 
        @inRank := @inRank+1, @score := a.Score 
FROM (SELECT Score FROM Scores ORDER BY Score DESC) a, 
    (SELECT @score := NULL, @rank := 0, @inRank := 1) b
) s;

说明：

  • 设定变量@score, @rank, @inRank，并分别初始赋值NULL, 0, 1；

  • 指定排序列的值不变时，@rank的值不变；指定排序列的值变化时，@rank的值跳变为@inRank内部计数的值；

  • @inRank变量每行自增1，用于内部计数。

三、部门工资前三高的员工【难】

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。

Id          Name        Salary      DepartmentId
1Joe700001
2Henry800002
3Sam600002
4Max900001
5Janet690001
6Randy850001

Department 表包含公司所有部门的信息。

Id  Name
1IT
2Sales

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

DepartmentEmployee  Salary    
ITMax90000
ITRandy85000
ITJoe70000
SalesHenry80000
SalesSam60000

MySQL查询：

SELECT Department.Name AS Department, e1.Name AS Employee, 
    e1.Salary AS Salary
FROM Employee e1
JOIN Department
ON e1.DepartmentId = Department.Id
WHERE 3 >= (
    SELECT COUNT(DISTINCT e2.Salary)
    FROM Employee e2
    WHERE e2.Salary >= e1.Salary 
	AND e1.DepartmentId = e2.DepartmentId)
ORDER BY Department.Name, e1.Salary DESC;
  •  

四、体育馆的人流量【难】

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

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。

例如，表 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 的增加而增加。

SELECT DISTINCT s4.*
FROM stadium s1, stadium s2, stadium s3, stadium s4
WHERE s2.id = s1.id + 1
AND s3.id = s1.id + 2
AND s1.people >= 100
AND s2.people >= 100
AND s3.people >= 100
AND s4.id IN (s1.id, s2.id, s3.id);
  •  

五、行程和用户【难】

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        |
+------------+-------------------+
SELECT 
    tc.Request_at Day, 
    IFNULL(ROUND((SUM(tc.cancel)/ SUM(tc.num)),2),0) 
        AS 'Cancellation Rate'
FROM (
    SELECT t.Request_at, t.Status, COUNT(*) num, 
    IF((t.Status = 'cancelled_by_client' 
        OR t.Status = 'cancelled_by_driver'),COUNT(*), 0) cancel
    FROM (
        SELECT t1.Id, t1.Status, t1.Request_at
        FROM Trips t1
    INNER JOIN (SELECT * FROM Users 
                WHERE Role = 'client' AND Banned = 'No') uc
    INNER JOIN (SELECT * FROM Users 
                WHERE Role = 'driver' AND Banned = 'No') ud
    ON uc.Users_id = t1.Client_id
    AND ud.Users_id = t1.Driver_id
    ) t
    GROUP BY t.Request_at, Status
) tc
GROUP BY tc.Request_at
HAVING tc.Request_at BETWEEN '2013-10-01' and '2013-10-03'
  •  

六、大的国家【易】

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

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

  • 注意：用UNION比用OR的运行时间快

七、超过5名学生的课【易】

有一个courses 表 ，有: student (学生) 和 class (课程)。列出所有超过或等于5名学生的课。

学生在每个课中不应被重复计算。

SELECT class 
    FROM courses
    GROUP BY class
    HAVING COUNT(DISTINCT student) >= 5;
  • 注意：（DISTINCT student）

八、交换工资【易】

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

UPDATE salary SET sex = IF(sex = 'm','f','m');
  • 仅用IF函数即可。

九、换座位【中】

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

其中纵列的 id 是连续递增的，小美想改变相邻俩学生的座位。

示例：

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

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

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

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

(SELECT DISTINCT s1.id id, (
    IF(s1.id % 2 = 1, s3.student, s2.student)
    ) student
    FROM seat s1, seat s2, seat s3
    WHERE (s1.id = s2.id + 1 AND s1.id = s3.id - 1) 
    OR (s1.id = 1 AND s1.id = s3.id - 1)
    OR (s1.id = (SELECT MAX(id) FROM seat) 
        AND s1.id % 2 = 0 AND s1.id = s2.id + 1) 
)
UNION 
(SELECT id, student FROM seat
    WHERE id = (SELECT MAX(id) FROM seat)
    AND EXISTS 
    (SELECT id FROM seat 
           WHERE id = (SELECT MAX(id) FROM seat) AND id % 2 = 1)
)
ORDER BY id;
  •  

完整数据库题目地址：https://leetcode-cn.com/problemset/database/

TreasureAI
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
LeetCode-SQL题目集
HeavenDan的博客
12-23 8176
序号 题号 题目 题解链接 标签 备注 1 176 176. 第二高的薪水 【LeetCode-SQL】176. 第二高的薪水 2 184 184. 部门工资最高的员工 【LeetCode-SQL】184. 部门工资最高的员工 3 4 5 6 7 8 9 10 .........
MySQL数据查询SQL
weixin_43701183的博客
02-26 479
MySQL数据查询SQL SQL查询语句：SELECT 先给出一张表 检索数据select 检索单个列 select 字段 from tbname; # 例： select name from stu; 如果没有明确排序规则，则返回的数据的顺序，则返回的数据的顺序没有特殊意义。 返回数据的顺序可能是数据被添加到表中的顺序，也可能不是 。只要返回相同的数目，就是正常的。 检索多个列 select 字段1,字段2,... from tbname; # 例： select id,name,age,sex
力扣(LeetCode)高频SQL50题(部分) ---- SQL学习笔记
weixin_52010459的博客
01-07 891
此为本人复盘使用笔记，有不足之处请大家不吝指出。
数据开放最全sql面试合集(leetcode)
最新发布
m0_55901666的博客
05-23 985
数据分析，数据开发常见sql面试题合集，大厂常见sql面试题，连续登录，TopN等常见面试题
SQL_leetcode整理
kaimingD的博客
04-13 792
目录 1.Not Boring Movies（620） 题目描述 题目类型 样例参考 代码思路 代码 测试结果 2.Rank Scores（178） 题目描述 题目类型 样例参考 思路 代码 测试结果 3.Delete Duplicate Emails（196） 题目描述 题目类型 样例参考 思路 代码 测试结果 4.Consecutive Num...
【学习笔记】LeetCode SQL刷题（高频50基础版+进阶版）
zhou_zzzzhou的博客
12-21 3632
584. 寻找用户推荐人 595. 大的国家 1148. 文章浏览 I 1683. 无效的推文 1378. 使用唯一标识码替换员工ID 1068. 产品销售分析 I 1581. 进店却未进行过交易的顾客 197. 上升的温度 1661. 每台机器的进程平均运行时间 11.577. 员工奖金 12.1280. 学生们参加各科测试的次数 13.570. 至少有5名直接下属的经理 14.1934. 确认率 15.620. 有趣的电影 16.1251. 平均售价
Leetcode SQL 解析（Day1）
Nina的博客
02-19 230
Leetcode会员题目SQL整理，窗口函数应用
【MySQL】查询顺序与查询SQL
weixin_43091333的博客
03-14 332
【MySQL】查询顺序与查询SQL SQL执行过程 执行过程 我们发送一条SQL给数据库，其实数据库并没有马上执行，而是进行预处理，对SQL进行分析和优化，可以说最终执行的SQL不一定是我们提交的SQL。整个过程非常繁杂，详细过程见下图： 虚拟表 数据库提供临时表、内存表、永久表，虚拟表一般要么是临时表，要么是内存表，用完就删除。永久表则数据会存储在磁盘中。 游标 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。
SQL-Leetcode-Challenge：包含所有117个Leetcode问题及其解决方案，从MySQL的Easy到Hard
02-06
在SQL的世界里，LeetCode是一个不可或缺的资源，它提供了丰富的编程题目来提升你的数据库查询技能。这个名为"SQL-Leetcode-Challenge"的压缩包文件，就是专为SQL爱好者准备的一份宝藏，包含了117个从易到难的...
Leetcode_Solutions:leetcode python算法mysql数据库
03-19
＃ 标题 验收 困难 状态 0001 两次总和 46.50％ 简单 X 0002 加两个数字 35.50％ 中等的 X 0003 最长子串，无重复字符 31.50％ 中等的 X 0004 两个排序数组的中位数 31.20％ 难的 X 0005 最长回文子串 ...X
MySQL:leetcode sql练习
03-08
在"MySQL: LeetCode SQL练习"这个主题中，我们将深入探讨如何使用MySQL来解决LeetCode上的SQL问题。 首先，了解SQL基础至关重要。SQL（Structured Query Language）是用于管理和处理关系数据库的标准语言。它的主要...
leetcode答案-LeetCode-SQL-Summary:*LeetCodeSQL102题总结*本笔记旨在帮助人们有效地学习和复习SQL
06-30
leetcode 答案力扣 SQL 总结 最后更新于 2020 年 4 月 15 日，涵盖了 104 道 LeetCode SQL 问题 介绍 在完成了所有 102 道 LeetCode SQL 问题后，我有一种冲动，可以通过他们试图测试的主题来总结它们。 本笔记旨在帮助人们有效地学习和复习 SQL ，尤其是在试图快速了解某个主题时，例如复杂连接、窗口函数、递归 CTE 等。 对于第一稿，我主要关注T-SQL/MS SQL Server 。 Topcis 的 LeetCode SQL 问题 基本 # 标题 困难 解决方案 182 简单的 511 简单的 578 中等的 584 简单的 586 简单的 595 简单的 596 简单的 619 简单的 620 简单的 1050 简单的 1069 简单的 1076 简单的 1082 简单的 1141 简单的 1148 简单的 1149 中等的 SQL 命令 # 标题 困难 解决方案 196 简单的 627 简单的 加入 简单连接 # 标题 困难 解决方案 175 简单的 181 简单的 183 简单的 184 中等的 197 简单的 262
LeetCode_SQL
03-18
这个"LeetCode_SQL"压缩包很可能包含了一个用户在解决这些挑战时创建的代码库。 在SQL挑战中，你可能会遇到的知识点涵盖了SQL语言的各个核心部分，包括但不限于： 1. **基础查询**：如何使用`SELECT`语句从表中...
7天带你玩转Mysql数据库之SQL语句_MYSQL_
10-02
"7天带你玩转Mysql数据库之SQL语句"的学习资料旨在帮助初学者快速掌握MySQL的基础知识，并通过LeetCode的面试题来提升实战技能。本文将深入探讨MySQL中的SQL语句及其在实际操作中的应用。 SQL（Structured Query ...
leetcode刷题记录——SQL（入门）
GZW18620285024的博客
08-12 1511
sql
leetcode-sql-刷题汇总(补充ing)
weixin_40113390的博客
04-27 837
leetcode-sql-刷题汇总(175-180, 补充ing)
Leetcode之SQL（一）
weixin_42368982的博客
05-05 362
一、最大的国 方案一：UNION # Write your MySQL query statement below SELECT name,population,area FROM world WHERE area >= 3000000 UNION SELECT name,population,area FROM world WHERE population >= 25000000 UNION：是一种联合体。其主要是用于节省空间，默认
mysql四大排名函数(row_number、rank、dense_rank、ntile)使用简介
热门推荐
qq_31476455的博客
05-09 2万+
有如下表 1.row_number row_number在排名时序号 连续 不重复，即使遇到表中的两个3时亦如此 执行 select row_number() OVER(order by e.salary desc) as row_num , e.salary from employee e 结果如图 注意：在使用row_number实现分页时需要特别注意一点，over子句中的order by...
leetcode-sql
qq_22179091的博客
06-24 179
1、Second Highest SalaryWrite a SQL query to get the second highest salary from the Employee table.+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+...
mysql dense_rank
09-02
- *1* [leetcode曼哈顿距离-study_SQL:数据分析SQL代码](https://download.csdn.net/download/weixin_38538585/19934420)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值