【LeetCode】--- MySQL刷题集合

1.组合两个表（外连接）

select p.firstName,p.lastName,a.city,a.state 
from Person p left join Address a 
on p.personId = a.personId;

以左边表为基准，去连接右边的表。取两表的交集和左表的全集 

2.第二高的薪水 （子查询、ifnull）

解法一（子查询与LIMIT 子句）：

limit的两种写法

limit 起始索引，查询数据的个数

limit 起始索引 offset 查询数据的个数

select 
(select distinct salary from Employee order by salary desc limit 1 offset 1) 
as SecondHighestSalary;

这样的写法就类似于

select 1+2 as SecondHighestSalary;

不需要有数据来源，因为这个表达式的计算结果已经是一个数据了。

这里只是给数据起一个别名。若是子查询数据为空，那么返回的就是null 而不是空了

总之。在 SQL 中使用子查询而没有 FROM 子句的情况通常是为了计算一个表达式或获取一个基于特定逻辑的单一结果，子查询本身提供了数据来源和处理逻辑，无需再通过 FROM 从物理表中获取数据。但在实际应用中，需要考虑性能和可维护性，避免过度复杂的子查询结构。 

解法二（使用 IFNULL 和 LIMIT 子句）： 

select ifnull(
(select distinct salary from Employee order by salary desc limit 1,1),null
) 
as SecondHighestSalary;

由于若查询结果为空，返回null

因此使用 ifnull 流程控制函数 更加合适

ifnull（value1，value2）

如果value1为null，就返回value2

如果不为空，就返回value1

注意，若是value1为一个sql语句，要给它加上括号

 3.第N高的薪水（函数、limit不能跟表达式）

 

答案： 

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M INT;
    set M = N-1;
  RETURN (
      select distinct salary from Employee order by salary desc limit M,1
  );
END

注意，limit中参数不能写成表达式的形式。也就是不能写成N-1

需要单独定义一个变量 M = N-1 

代码解释 

这个 SQL 代码创建了一个名为 getNthHighestSalary 的函数，该函数接收一个整数参数 N，用于表示要查找第 N 高的薪水。函数的返回值是一个整数，代表第 N 高的薪水值。

变量声明：

declare M INT;

变量赋值：

set M = N-1;


查询语句

RETURN (
    select salary from Employee order by salary desc limit M,1
);

 4.分数排名（窗口函数（排名函数dense_rank(））

窗口函数的基本结构：

<窗口函数>([参数]) OVER (
    [PARTITION BY <分区列1>, <分区列2>,...]
    [ORDER BY <排序列1> [ASC | DESC], <排序列2> [ASC | DESC],...]
    [ROWS | RANGE <窗口范围说明>]
)

各部分解释：

窗口函数：

  • 这是核心部分，可以是聚合函数（如 SUM()、AVG()、COUNT()、MAX()、MIN() 等）或排名函数（如 RANK()、DENSE_RANK()、ROW_NUMBER() 等）。

  • OVER 子句：

    • 是窗口函数的关键字，表明后面的内容是对窗口的定义。
  • PARTITION BY：
    这是可选的。用于将数据划分为不同的分区，类似于 GROUP BY 的功能，但不会像 GROUP BY 那样对数据进行聚合操作。窗口函数会在每个分区内独立执行。
    例如：partition by ... 
    PARTITION BY department
    会将数据按照部门进行分区，窗口函数将在每个部门内分别计算。
  • ORDER BY：
    通常是必需的，用于对分区内的数据进行排序。这会影响排名函数的结果，以及聚合函数的计算顺序。
    例如： 
    ORDER BY salary DESC
    会将分区内的数据按照薪水降序排列。
  • ROWS | RANGE 窗口范围说明：
    这也是可选的，用于进一步定义窗口的范围。
    ROWS 基于物理行，例如 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    表示当前行的前一行到后一行的范围。
    RANGE 基于逻辑值，例如 
     RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
    表示在当前行的值的基础上，范围是比当前行的值小 10 到大 10 的数据范围。

 排名函数（如 RANK()、DENSE_RANK()、ROW_NUMBER() 等）。

方法一：排名函数dense_rank(）（推荐）

rank()               排名的特点是  1 2 2 4 5 5 5 8 9

dense_rank(） 排名的特点是  1 2 2 3 4 5 5 5 6

ROW_NUMBER()      特点是   1 2 3 4 5 6 7 8 9

select s.score,dense_rank() 
over(order by s.score desc) as 'rank' 
from Scores s;

 方法二：使用 COUNT(DISTINCT ...) 的相关子查询

SELECT
  S1.score,
  (
    SELECT
      COUNT(DISTINCT S2.score)
    FROM
      Scores S2
    WHERE
      S2.score >= S1.score
  ) AS 'rank'
FROM
  Scores S1
ORDER BY
  S1.score DESC;

这段 SQL 代码的主要目的是为 Scores 表中的每个 score 计算排名。它使用了一个相关子查询来计算排名，排名的依据是大于或等于当前分数的不同分数的数量。

主查询：

SELECT S1.score：从 Scores 表中选择 score 列作为主查询的一部分。

子查询部分：

COUNT(DISTINCT S2.score)：计算不同分数的数量。
FROM Scores S2：从 Scores 表中选取数据。
WHERE S2.score >= S1.score：这是关键部分，对于主查询中的每个 S1.score，子查询会统计 Scores 表中大于或等于 S1.score 的不同分数的数量。

 最终结果是通过 ORDER BY S1.score DESC 对主查询的结果按照分数降序排列。

5.连续出现的数字 (自连接)

自连接使用场景

1.比较同一表中不同行的数据：

  • 示例场景：查找表中相邻行的数据关系，例如找出连续出现的记录。

2.查找父子关系或层次关系：

  • 示例场景：在存储了层次结构信息的表中查找父子节点关系。

3.找出重复记录：

  • 示例场景：找出表中具有相同数据的行。

4.时间序列分析：

  • 示例场景：在存储了时间序列数据的表中，找出连续时间点的数据。

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;

找出至少出现三次的数字。因此进行自连接三次

条件是

第一张表id 等于 第二张表 id-1

第二张表id 等于 第三张表 id-1

且

第一张表num 等于 第二张表 num

第二张表num 等于 第三张表 num

6.超过经理收入的员工（自连接）

解法一：自连接（隐式where语句）

select e1.name as Employee
from Employee e1,Employee e2 
where e1.managerId = e2.id and e1.salary > e2.salary;

SQL解释 

表的自连接：

from Employee e1, Employee e2

这里将 Employee 表自连接，使用别名 e1 表示员工，e2 表示经理。

where e1.managerId = e2.id and e1.salary > e2.salary;

 e1.managerId = e2.id：
这是自连接的关键条件，它将 e1 表中的 managerId 与 e2 表中的 id 进行连接，意味着 e2 表中的员工是 e1 表中员工的经理。
e1.salary > e2.salary：
这是筛选条件，它确保只选择那些员工的工资（e1.salary）高于其经理的工资（e2.salary）的记录。

 解法二：自连接（显示on语句）

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;

 一、自连接过程：
首先，将 Employee 表自连接，形成笛卡尔积。这意味着 e1 和 e2 表的组合将包含所有可能的行对，即每个 e1 中的行将与 e2 中的所有行组合在一起，总共会有  种组合（假设 Employee 表有 n 行）。对于我们的示例表，会有6的平方 = 36  种组合，但很多组合将不符合条件。

e1.managerId = e2.id：
这一条件将确保 e1 中的员工的经理是 e2 中的员工。例如：
对于 e1 中的 Bob（id = 2，managerId = 1），只有当 e2 中的 id = 1 时，这个条件才满足。所以，Bob 可以和 Alice 组合。 

e1.salary > e2.salary：
在上述满足 e1.managerId = e2.id 的组合中，进一步筛选出员工（e1）工资高于经理（e2）工资的组合：
对于 Bob（e1）和 Alice（e2）的组合：Bob 的工资是 6000，Alice 的工资是 5000，满足 e1.salary > e2.salary，所以 Bob 会被选中。 

注意e1.managerId = e2.id 与 e1.id = e2.managerId的区别

e1.managerId = e2.id 
这个条件表示 e2 表中的员工是 e1 表中员工的经理。也就是说，通过 managerId 建立了从员工（e1）到其经理（e2）的关联。 

e1.id = e2.managerId：
这个条件表示 e1 表中的员工是 e2 表中员工的经理。与第一个语句相反，这里是通过 managerId 建立了从经理（e1）到其下属员工（e2）的关联。 

7.找到重复的电子邮箱(自连接 | group by... having)

法一：自连接（效率低一点）

select distinct p1.email as Email 
from Person p1, Person p2 
where p1.id <> p2.id and p1.email = p2.email;

法二：（GROUP BY 和临时表） （比自连接效率高）

表子查询

先

select email, count(*) as num from Person group by email

得到临时表

再根据这个表找到email数量大于 1 的 email就可以了

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

法三、使用 GROUP BY 和 HAVING 条件 （效率相对高一点）

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

分完组之后，再用having count（*）来计算组内的行数。

再筛选组内行数大于1的email 

 8.从不订购的客户（is null）

 注意：判断是否为null 要用 is 而不是 =

法一：列子查询（子查询返回的是一列数据）

select name as Customers from Customers
where id 
not in (select customerId from Orders);

法二：左连接（Left Join）

select name as Customers from Customers c
left join Orders o 
on c.id = o.customerId  where customerId is null;

9.部门工资最高的员工 

法一：隐式内连接

select d.name Department,e.name Employee,e.salary 
from Employee e,Department d
where e.departmentId = d.id
and e.salary >= 
all (select salary from Employee t where e.departmentId = t.departmentId);

e.salary >= 
all (select salary from Employee t where e.departmentId = t.departmentId);

员工薪资 ≥ 相同部门的薪资。

法二、窗口函数（MAX（）+行子查询） 

select d.name AS 'Department',e.name AS 'Employee',Salary
FROM Employee e JOIN Department d ON e.DepartmentId = d.Id
where (e.DepartmentId , Salary) in
(select DepartmentId, MAX(Salary) from Employee 
GROUP BY DepartmentId);

 where 条件 绑定了部门 id 和 薪资水平 in

查出来的 部门id 和最高的薪资水平。

select DepartmentId, MAX(Salary) from Employee 
GROUP BY DepartmentId;

法三：窗口函数（dense_rank() partition by）(推荐)

select d.name AS 'Department',e.name AS 'Employee',e.salary Salary
from 
(select *,dense_rank()  over(partition by departmentId order by salary desc) as erank 
from Employee) e ,Department d 
where e.departmentId = d.Id
and erank <= 1;

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

 

法一：窗口函数（dense_rank()） 

select d.name as Department, e.name as Employee,salary Salary from
(select *, dense_rank()
over(partition by departmentId order by salary desc) as erank
from Employee) e
left join Department d on e.departmentId = d.id
where erank <= 3;

其中 

select *, dense_rank()
over(partition by departmentId order by salary desc) as erank
from Employee;

#根据部门id分组，再对组内按照薪水从大到小排序，并生成对应的排名编号

再右连接Deparment表 输出排名编号≤3的

 

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/957711.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【C++】模板（进阶）

【C++】模板（进阶）

本篇我们来介绍更多关于C模板的知识。模板初阶移步至&#xff1a;【C】模板&#xff08;初阶&#xff09; 1.非类型模板参数 1.1 非类型模板参数介绍 模板参数可以是类型形参&#xff0c;也可以是非类型形参。类型形参就是我们目前接触到的一些模板参数。 //类型模板参数 …
阅读更多...
ASP .NET Core 学习（.NET9）配置接口访问路由

ASP .NET Core 学习（.NET9）配置接口访问路由

新创建的 ASP .NET Core Web API项目中Controller进行请求时&#xff0c;是在地址:端口/Controller名称进行访问的&#xff0c;这个时候Controller的默认路由配置如下 访问接口时&#xff0c;是通过请求方法&#xff08;GET、Post、Put、Delete&#xff09;进行接口区分的&…
阅读更多...
用于牙科的多任务视频增强

用于牙科的多任务视频增强

Multi-task Video Enhancement for Dental Interventions 2022 miccai Abstract 微型照相机牢牢地固定在牙科手机上&#xff0c;这样牙医就可以持续地监测保守牙科手术的进展情况。但视频辅助牙科干预中的视频增强减轻了低光、噪音、模糊和相机握手等降低视觉舒适度的问题。…
阅读更多...
一部手机如何配置内网电脑同时访问内外网

一部手机如何配置内网电脑同时访问内外网

做过运维的朋友都知道&#xff0c;最麻烦的是运维电脑不能远程&#xff0c;每次都得现场进行维护&#xff0c;明明客户那边有可以访问内网的电脑&#xff0c;怎么操作能将这台电脑能访问跟到外网呢&#xff0c;这样不就能通过远程软件远程了吗&#xff1f;嘿嘿。按以下步骤试试…
阅读更多...
基于STM32的智能门锁安防系统（开源）

基于STM32的智能门锁安防系统（开源）

目录 项目演示 项目概述 硬件组成&#xff1a; 功能实现 1. 开锁模式 1.1 按键密码开锁 1.2 门禁卡开锁 1.3 指纹开锁 2. 功能备注 3. 硬件模块工作流程 3.1 步进电机控制 3.2 蜂鸣器提示 3.3 OLED显示 3.4 指纹与卡片管理 项目源代码分析 1. 主程序流程 (main…
阅读更多...
(三)线性代数之二阶和三阶行列式详解

(三)线性代数之二阶和三阶行列式详解

在前端开发中&#xff0c;尤其是在WebGL、图形渲染、或是与地图、模型计算相关的应用场景里&#xff0c;行列式的概念常常在计算变换矩阵、进行坐标变换或进行图形学算法时被使用。理解二阶和三阶行列式对于理解矩阵运算、旋转、平移等操作至关重要。下面&#xff0c;我将结合具…
阅读更多...
基于GRU实现股价多变量时间序列预测(PyTorch版)

基于GRU实现股价多变量时间序列预测(PyTorch版)

前言 系列专栏:【深度学习：算法项目实战】✨︎ 涉及医疗健康、财经金融、商业零售、食品饮料、运动健身、交通运输、环境科学、社交媒体以及文本和图像处理等诸多领域，讨论了各种复杂的深度神经网络思想，如卷积神经网络、循环神经网络、生成对抗网络、门控循环单元、长短期记…
阅读更多...
【EdgeAI实战】（1）STM32 边缘 AI 生态系统

【EdgeAI实战】（1）STM32 边缘 AI 生态系统

【EdgeAI实战】&#xff08;1&#xff09;STM32 边缘 AI 生态系统 【EdgeAI实战】&#xff08;1&#xff09;STM32 边缘 AI 生态系统 1. STM32 边缘人工智能1.1 X-CUBE-AI 扩展包1.2 STM32 AI Model Zoo1.3 ST AIoT Craft 2. STM32N6 AI 生态系统 (STM32N6-AI)2.1 STM32N6 AI 产…
阅读更多...
DeepSeek-R1性能如何？如何使用DeepSeek-R1和o1 Pro模型

DeepSeek-R1性能如何？如何使用DeepSeek-R1和o1 Pro模型

我们一起来看看DeepSeek-R1模型和OpenAI o1模型的能力如何&#xff1f;接下来&#xff0c;我们先看数据结果&#xff0c;然后再实际体验&#xff0c;我们今天就让他们写个python爬虫脚本来爬取所有有关孙颖莎和樊振东的相关报道和图片。 DeepSeek-R1 DeepSeek介绍自己说 &quo…
阅读更多...
FunASR语言识别的环境安装、推理

FunASR语言识别的环境安装、推理

目录 一、环境配置 1、创建虚拟环境 2、安装环境及pytorch 官网&#xff1a;pytorch下载地址 3、安装funasr之前&#xff0c;确保已经安装了下面依赖环境: python代码调用&#xff08;推荐&#xff09; 4、模型下载 5、启动funasr服务 二、 客户端连接 2.1 html连接 …
阅读更多...
【Elasticsearch】 Ingest Pipeline `processors`属性详解

【Elasticsearch】 Ingest Pipeline `processors`属性详解

在Elasticsearch中&#xff0c;Ingest Pipeline 的 processors 属性是一个数组&#xff0c;包含一个或多个处理器&#xff08;processors&#xff09;。每个处理器定义了一个数据处理步骤&#xff0c;可以在数据索引之前对数据进行预处理或富化。以下是对 processors 属性中常见…
阅读更多...
架构思考与实践：从通用到场景的转变

架构思考与实践：从通用到场景的转变

在当今复杂多变的商业环境中&#xff0c;企业架构的设计与优化成为了一个关键议题。本文通过一系列随笔&#xff0c;探讨了业务架构的价值、从通用架构到场景架构的转变、恰如其分的架构设计以及如何避免盲目低效等问题。通过对多个实际案例的分析&#xff0c;笔者揭示了架构设…
阅读更多...
消息队列实战指南：三大MQ 与 Kafka 适用场景全解析

消息队列实战指南：三大MQ 与 Kafka 适用场景全解析

前言&#xff1a;在当今数字化时代&#xff0c;分布式系统和大数据处理变得愈发普遍&#xff0c;消息队列作为其中的关键组件&#xff0c;承担着系统解耦、异步通信、流量削峰等重要职责。ActiveMQ、RabbitMQ、RocketMQ 和 Kafka 作为市场上极具代表性的消息队列产品&#xff0…
阅读更多...
win32汇编环境,怎么得到磁盘的盘符

win32汇编环境,怎么得到磁盘的盘符

;运行效果 ;win32汇编环境,怎么得到磁盘的盘符 ;以下代码主要为了展示一下原理&#xff0c;应用GetLogicalDrives、GetLogicalDriveStrings函数、屏蔽某些二进制位、按双字节复制内容等。以下代码最多查8个盘&#xff0c;即返回值中的1个字节的信息 ;直接抄进RadAsm可编译运行。…
阅读更多...
微软预测 AI 2025，AI Agents 重塑工作形式

微软预测 AI 2025，AI Agents 重塑工作形式

1月初&#xff0c;微软在官网发布了2025年6大AI预测&#xff0c;分别是&#xff1a;AI模型将变得更加强大和有用、AI Agents将彻底改变工作方式、AI伴侣将支持日常生活、AI资源的利用将更高效、测试与定制是开发AI的关键以及AI将加速科学研究突破。 值得一提的是&#xff0c;微…
阅读更多...
网络编程套接字（二）

网络编程套接字（二）

目录 TCP网络程序 服务端初始化 创建套接字 服务端绑定 服务端监听 服务端启动 服务端获取连接 服务端处理请求 客户端初始化 客户端启动 发起连接 发起请求 网络测试 多进程版TCP网络程序 捕捉SIGCHLD信号 孙子进程提供服务 多线程版TCP网络程序 线程池版TC…
阅读更多...
网站HTTP改成HTTPS

网站HTTP改成HTTPS

您不仅需要知道如何将HTTP转换为HTTPS&#xff0c;还必须在不妨碍您的网站自成立以来建立的任何搜索排名权限的情况下进行切换。 为什么应该从HTTP转换为HTTPS&#xff1f; 与非安全HTTP于不同&#xff0c;安全域使用SSL&#xff08;安全套接字层&#xff09;服务器上的加密代…
阅读更多...
渗透测试--攻击常见的Web应用

渗透测试--攻击常见的Web应用

本文章咱主要讨论&#xff0c;常见Web应用的攻击手法&#xff0c;其中并不完全&#xff0c;因为Web应用是在太多无法囊括全部&#xff0c;但其中的手法思想却值得我们借鉴&#xff0c;所以俺在此做了记录&#xff0c;希望对大家有帮助&#xff01;主要有以下内容&#xff1a; 1…
阅读更多...
外包公司名单一览表（成都）

外包公司名单一览表（成都）

大家好&#xff0c;我是苍何。 之前写了一篇武汉的外包公司名单&#xff0c;评论区做了个简单统计&#xff0c;很多人说&#xff0c;在外包的日子很煎熬&#xff0c;不再想去了。 有小伙伴留言说有些外包会强制离职&#xff0c;不行就转岗&#xff0c;让人极度没有安全感。 这…
阅读更多...
2025 最新flutter面试总结

2025 最新flutter面试总结

目录 1.Dart是值传递还是引用传递&#xff1f; 2.Flutter 是单引擎还是双引擎 3. StatelessWidget 和 StatefulWidget 在 Flutter 中有什么区别&#xff1f; 4.简述Dart语音特性 5. Navigator 是什么&#xff1f;在 Flutter 中 Routes 是什么&#xff1f; 6、Dart 是不是…
阅读更多...
最新文章

Copyright @ 2022~2023