【LeetCode】--- MySQL刷题集合

25-03-07 17:21

7237

blog.csdn.net

id="article_content" class="article_content clearfix"> id="content_views" class="htmledit_views">

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1">

class="hljs-ln-code"> class="hljs-ln-line">select p.firstName,p.lastName,a.city,a.state

class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2">

 class="hljs-ln-code"> class="hljs-ln-line">from Person p left join Address a  class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">on p.personId = a.personId;

class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

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

limit的两种写法

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

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">(select distinct salary from Employee order by salary desc limit 1 offset 1) 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">as SecondHighestSalary; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

这样的写法就类似于
select 1+2 as SecondHighestSalary;
 class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">
不需要有数据来源，因为这个表达式的计算结果已经是一个数据了。

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

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

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select ifnull(
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">(select distinct salary from Employee order by salary desc limit 1,1),null
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">) 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">as SecondHighestSalary;
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">  class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

ifnull（value1，value2）

如果value1为null，就返回value2

如果不为空，就返回value1

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

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

答案：

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">BEGIN
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">declare M INT;
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">    set M = N-1;
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">  RETURN (
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="6"> class="hljs-ln-code"> class="hljs-ln-line">      select distinct salary from Employee order by salary desc limit M,1
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="7"> class="hljs-ln-code"> class="hljs-ln-line">  );
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="8"> class="hljs-ln-code"> class="hljs-ln-line">END class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

代码解释

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

变量声明：

declare M INT; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

变量赋值：

set M = N-1; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

查询语句

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">RETURN (
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">    select salary from Employee order by salary desc limit M,1
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">); class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

窗口函数的基本结构：

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line"><窗口函数>([参数]) OVER (
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">    [PARTITION BY <分区列1>, <分区列2>,...]
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">    [ORDER BY <排序列1> [ASC | DESC], <排序列2> [ASC | DESC],...]
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">    [ROWS | RANGE <窗口范围说明>]
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">) class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

各部分解释：

窗口函数：

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

OVER 子句：

是窗口函数的关键字，表明后面的内容是对窗口的定义。
PARTITION BY：
这是可选的。用于将数据划分为不同的分区，类似于 GROUP BY 的功能，但不会像 GROUP BY 那样对数据进行聚合操作。窗口函数会在每个分区内独立执行。
例如：partition by ...
PARTITION BY department  class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">
会将数据按照部门进行分区，窗口函数将在每个部门内分别计算。
ORDER BY：
通常是必需的，用于对分区内的数据进行排序。这会影响排名函数的结果，以及聚合函数的计算顺序。
例如：
ORDER BY salary DESC class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">
会将分区内的数据按照薪水降序排列。
ROWS | RANGE 窗口范围说明：
这也是可选的，用于进一步定义窗口的范围。
ROWS 基于物理行，例如
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">
表示当前行的前一行到后一行的范围。
RANGE 基于逻辑值，例如
 RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">
表示在当前行的值的基础上，范围是比当前行的值小 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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select s.score,dense_rank() 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">over(order by s.score desc) as 'rank' 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">from Scores s; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">SELECT
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">  S1.score,
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">  (
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">    SELECT
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">      COUNT(DISTINCT S2.score)
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="6"> class="hljs-ln-code"> class="hljs-ln-line">    FROM
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="7"> class="hljs-ln-code"> class="hljs-ln-line">      Scores S2
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="8"> class="hljs-ln-code"> class="hljs-ln-line">    WHERE
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="9"> class="hljs-ln-code"> class="hljs-ln-line">      S2.score >= S1.score
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="10"> class="hljs-ln-code"> class="hljs-ln-line">  ) AS 'rank'
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="11"> class="hljs-ln-code"> class="hljs-ln-line">FROM
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="12"> class="hljs-ln-code"> class="hljs-ln-line">  Scores S1
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="13"> class="hljs-ln-code"> class="hljs-ln-line">ORDER BY
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="14"> class="hljs-ln-code"> class="hljs-ln-line">  S1.score DESC; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

这段 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.时间序列分析：

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select distinct l1.num as ConsecutiveNums
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">from Logs l1,Logs l2,Logs l3
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">where 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">l1.id = l2.id-1 and
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">l2.id = l3.id-1 and
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="6"> class="hljs-ln-code"> class="hljs-ln-line">l1.num = l2.num and
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="7"> class="hljs-ln-code"> class="hljs-ln-line">l2.num = l3.num; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

条件是

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

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

且

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

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

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

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select e1.name as Employee
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">from Employee e1,Employee e2 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">where e1.managerId = e2.id and e1.salary > e2.salary;  class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

SQL解释

表的自连接：
from Employee e1, Employee e2 class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">
这里将 Employee 表自连接，使用别名 e1 表示员工，e2 表示经理。
where e1.managerId = e2.id and e1.salary > e2.salary; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">
e1.managerId = e2.id：
这是自连接的关键条件，它将 e1 表中的 managerId 与 e2 表中的 id 进行连接，意味着 e2 表中的员工是 e1 表中员工的经理。
e1.salary > e2.salary：
这是筛选条件，它确保只选择那些员工的工资（e1.salary）高于其经理的工资（e2.salary）的记录。

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">SELECT
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">     a.NAME AS Employee
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">FROM Employee AS a JOIN Employee AS b
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">     ON a.ManagerId = b.Id
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">     AND a.Salary > b.Salary
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="6"> class="hljs-ln-code"> class="hljs-ln-line">;
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="7"> class="hljs-ln-code"> class="hljs-ln-line">  class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

一、自连接过程：
首先，将 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)

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select distinct p1.email as Email 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">from Person p1, Person p2 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">where p1.id <> p2.id and p1.email = p2.email;  class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

表子查询

先

select email, count(*) as num from Person group by email class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

得到临时表

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select  email as Email 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">from  (select email, count(*) as num from Person group by email) t
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">where num > 1; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select email from Person 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">group by email 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">having count(*) > 1; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

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

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

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select name as Customers from Customers
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">where id 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">not in (select customerId from Orders); class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

法二：左连接（Left Join）

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select name as Customers from Customers c
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">left join Orders o 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">on c.id = o.customerId  where customerId is null;  class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

9.部门工资最高的员工

法一：隐式内连接

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select d.name Department,e.name Employee,e.salary 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">from Employee e,Department d
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">where e.departmentId = d.id
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">and e.salary >= 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">all (select salary from Employee t where e.departmentId = t.departmentId); class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">e.salary >= 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">all (select salary from Employee t where e.departmentId = t.departmentId); class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select d.name AS 'Department',e.name AS 'Employee',Salary
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">FROM Employee e JOIN Department d ON e.DepartmentId = d.Id
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">where (e.DepartmentId , Salary) in
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">(select DepartmentId, MAX(Salary) from Employee 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">GROUP BY DepartmentId); class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select DepartmentId, MAX(Salary) from Employee 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">GROUP BY DepartmentId; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select d.name AS 'Department',e.name AS 'Employee',e.salary Salary
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">from 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">(select *,dense_rank()  over(partition by departmentId order by salary desc) as erank 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">from Employee) e ,Department d 
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">where e.departmentId = d.Id
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="6"> class="hljs-ln-code"> class="hljs-ln-line">and erank <= 1; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select d.name as Department, e.name as Employee,salary Salary from
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">(select *, dense_rank()
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">over(partition by departmentId order by salary desc) as erank
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="4"> class="hljs-ln-code"> class="hljs-ln-line">from Employee) e
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="5"> class="hljs-ln-code"> class="hljs-ln-line">left join Department d on e.departmentId = d.id
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="6"> class="hljs-ln-code"> class="hljs-ln-line">where erank <= 3; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

其中

 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="1"> class="hljs-ln-code"> class="hljs-ln-line">select *, dense_rank()
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="2"> class="hljs-ln-code"> class="hljs-ln-line">over(partition by departmentId order by salary desc) as erank
 class="hljs-ln-numbers"> class="hljs-ln-line hljs-ln-n" data-line-number="3"> class="hljs-ln-code"> class="hljs-ln-line">from Employee; class="hljs-button signin active" data-title="登录复制" data-report-click="{"spm":"1001.2101.3001.4334"}" onclick="hljs.signin(event)">

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

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

data-report-view="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://hehuanshu.blog.csdn.net/article/details/145281485","extend1":"pc","ab":"new"}">>

注：本文转载自blog.csdn.net的振兴祁门的文章"https://blog.csdn.net/m0_73456341/article/details/145281485"。版权归原作者所有，此博客不拥有其著作权，亦不承担相应法律责任。如有侵权，请联系我们删除。

复制链接

发表评论

/ 登录

评论记录：

未查询到任何数据！