【实战】一些中高级sql面试题-1

1-部门查询-递归

给定一个部门表 departments，其中包含字段 id（部门ID）、name（部门名称）、parent_id（父部门ID）。请编写SQL查询，找出某个部门（例如 id = 5）的所有子部门（包括直接子部门和间接子部门）。

表结构如下:

departments (
    id INT,
    name VARCHAR(50),
    parent_id INT
)

先思考下呢，似乎很简单：

SELECT * FROM departments WHERE id = 5 OR parent_id = 5;

结果出来啦，对吗？似乎有点不正确，题目中说的是 ，直接子部门和间接子部门。

上述sql没能查找间接子部门。

假设数据

假设 departments 表中有以下数据：

id	name	parent_id
1	总部	NULL
2	技术部	1
3	市场部	1
4	研发部	2
5	工程部	2
6	测试部	5
7	运维部	5
8	子测试部	6

如果要包含 直接子部门和间接子部门，结果应该为：

id	name	parent_id
5	工程部	2
6	测试部	5
7	运维部	5
8	子测试部	6

其中 子测试部 就是所谓的间接子部门，请问此时该如何查找呢。可以通过一下sql：

WITH RECURSIVE dept_tree AS (
    -- 基础情况：选择指定部门（id = 5）
    SELECT id, name, parent_id
    FROM departments
    WHERE id = 5
    UNION ALL
    -- 递归部分：查找子部门
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

分析与解答

• 查询逻辑分解

首先是 非递归部分

SELECT id, name, parent_id
FROM departments
WHERE id = 5

接着是 递归部分

SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN dept_tree dt ON d.parent_id = dt.id  -- 关键点

通过自连接查找dept_tree当前结果的所有子部门：

• d.parent_id = dt.id：找到父部门是dept_tree中某部门的记录。
• 每次递归时，dept_tree会包含上一步新添加的子部门，实现逐层向下查询。

合并结果-union all

UNION ALL  -- 合并基础部分和递归部分的结果

在 MySQL 中，UNION ALL 是一个用于合并多个 SELECT 查询结果集的操作符。它的核心特点是：

核心作用

• 保留所有行：UNION ALL 会将多个查询的结果集直接合并，包括重复的行。
• 性能更高：相比 UNION（自动去重），UNION ALL 不需要进行去重操作，因此执行速度更快。

与 UNION 的区别

特性	UNION	UNION ALL
重复行处理	自动去除重复行（去重）	保留所有行（不去重）
性能	较慢（需排序和去重）	更快（无需去重）
结果顺序	可能改变原始顺序	保留原始顺序
使用场景	需要唯一结果时（如客户列表）	需要完整结果或已知无重复时

最终输出

SELECT * FROM dept_tree;  -- 返回所有匹配的部门

递归CTE的核心特性：

1. 分步执行机制：
   • 第一步：运行基础部分（id=5），生成初始结果集（假设叫R0）。
   • 第二步：将R0代入递归部分，执行JOIN dept_tree（此时dept_tree = R0），得到子部门结果R1。
   • 第三步：将R1代入递归部分（dept_tree = R1），得到孙子部门R2。
   • 重复直到递归部分返回空集（没有更多子部门）。
2. 递归引用合法性：
   • dept_tree在递归部分中被引用，是SQL标准允许的语法。
   • 数据库引擎会暂存每一步的中间结果，用于下一次递归，直到终止。

详细执行步骤

以下是详细执行步骤，可以帮助我们理解：

1. 初始步骤（非递归部分）：

   SELECT id, name, parent_id
   FROM departments
   WHERE id = 5
   

   结果：(5, '总部', NULL) → 作为初始结果集R0

2. 第一次递归（使用R0作为dept_tree）：

   SELECT d.id, d.name, d.parent_id
   FROM departments d
   INNER JOIN dept_tree dt ON d.parent_id = dt.id  -- 此时dt = R0 (id=5)
   

   结果：查找所有parent_id=5的子部门 → 假设得到(8, '技术部', 5) → 作为结果集R1

3. 合并结果： UNION ALL 将 R0 + R1 → 当前结果：[5, 8]

4. 第二次递归（使用R1作为dept_tree）：

   SELECT d.id, d.name, d.parent_id
   FROM departments d
   INNER JOIN dept_tree dt ON d.parent_id = dt.id  -- 此时dt = R1 (id=8)
   

   结果：查找所有parent_id=8的孙子部门 → 假设得到(9, '前端', 8)和(10, '后端', 8) → R2

5. 再次合并： UNION ALL 将 R0 + R1 + R2 → 当前结果：[5, 8, 9, 10]

6. 第三次递归（使用R2作为dept_tree）：

SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN dept_tree dt ON d.parent_id = dt.id  -- 此时dt = R2 (id=9,10)

结果：查找parent_id=9或10的部门 → 没有结果 → 递归终止

括号外和最后的select

WITH RECURSIVE dept_tree AS (...) 的含义

1. WITH RECURSIVE：声明这是一个递归公用表表达式（Recursive CTE）
2. dept_tree：为这个递归查询结果集指定的临时表名
3. 括号内的SQL：定义递归查询的核心逻辑，包含：
   • 基础查询（起点）
   • 递归查询（迭代部分）
   • 两者通过UNION ALL连接

最后 SELECT * FROM dept_tree; 的含义

这是主查询语句，表示：

"从递归生成的临时表dept_tree中选择所有列并输出结果"

扩展

1. WITH RECURSIVE 是语法关键字：
   • WITH 表示公用表表达式 (CTE)
   • RECURSIVE 是特殊修饰符，声明这个CTE包含递归查询
   • 这是SQL标准语法（在PostgreSQL、MySQL 8.0+、SQLite等都支持）
2. 最后的 SELECT \* 可自定义：

-- 示例：只选择需要的列
SELECT id, name FROM dept_tree;

-- 示例：添加计算列
SELECT id, name, parent_id, 
       (SELECT COUNT(*) FROM employees WHERE department_id = dept_tree.id) AS employee_count
FROM dept_tree;

-- 示例：添加过滤条件
SELECT * FROM dept_tree WHERE name LIKE '%技术%';

3. 临时表名可随意命名：

-- 有效示例1
WITH RECURSIVE org_chart AS (...)
SELECT * FROM org_chart;

-- 有效示例2
WITH RECURSIVE department_hierarchy AS (...)
SELECT name FROM department_hierarchy;

• 临时表名需符合SQL标识符规则（字母开头，不含特殊字符等）
• 不能与现有表名冲突（仅在当前查询有效）

完整结构说明：

WITH RECURSIVE [自定义临时表名] AS (
    -- 基础查询（起点）
    SELECT ... 
    UNION ALL
    -- 递归查询（迭代部分）
    SELECT ... FROM [自定义临时表名] ...
)
-- 主查询（可任意操作临时表）
SELECT [列|表达式] FROM [自定义临时表名] [WHERE/GROUP BY/ORDER BY等];

注意：

在递归CTE（公用表表达式）中，基础查询（锚成员）和递归查询（递归成员）必须使用 UNION ALL 连接，这是SQL标准规定的语法要求。以下是详细说明：

必须使用 UNION ALL 的原因

1. 语法强制要求：
   • SQL标准规定递归CTE中两部分必须通过 UNION ALL 连接
   • 使用其他操作符（如 UNION, EXCEPT, JOIN）会导致语法错误
2. 数据累积需求：
   • UNION ALL 保留所有记录（包括重复记录）
   • 递归过程需要累积每一层的结果，不能去重（不同层级的部门本质不同）
3. 性能考量：
   • UNION 会去重并排序，增加不必要的开销
   • 递归查询本质是迭代累积，UNION ALL 是最高效的方式

2-多表关联与性能优化

给定三个表：users（用户表）、orders（订单表）、products（商品表）。请编写SQL查询，统计每个用户的购买商品种类数（不同 product_id 的数量），并按种类数降序排序。

users (
    user_id INT,
    name VARCHAR(50)
)

orders (
    order_id INT,
    user_id INT,
    product_id INT,
    order_time DATETIME
)

products (
    product_id INT,
    product_name VARCHAR(50)
)

需求分析

• 我们需要计算每个用户购买的唯一 product_id 数量。
• 需要包含用户信息（user_id 和 name），并按商品种类数降序排序。
• products 表仅提供 product_id 和 product_name，但此查询只关心 product_id 的数量，不需要商品名称，因此可以不使用 products 表。
• 考虑所有用户，包括未购买任何商品的用户（其种类数应为 0）。

假设数据

假设数据如下：

users:

user_id	name
1	Alice
2	Bob
3	Charlie

orders:

order_id	user_id	product_id	order_time
101	1	1	2023-01-01 10:00:00
102	1	2	2023-01-02 12:00:00
103	1	1	2023-01-03 14:00:00
104	2	3	2023-01-04 09:00:00

分析与解答

查询说明

1. 选择列
   • u.user_id：用户ID，标识每个用户。
   • u.name：用户名，提供用户可读信息。
   • COUNT(DISTINCT o.product_id) AS product_count：统计每个用户购买的不同 product_id 数量
2. 表连接
   • FROM users u：从 users 表开始，获取所有用户信息。
   • LEFT JOIN orders o ON u.user_id = o.user_id：使用左连接，确保即使某些用户没有订单记录，也能出现在结果中（其 product_count 将为 0）。
3. 分组
   • GROUP BY u.user_id, u.name：按用户ID和名称分组，计算每个用户的 product_count。
   • 注意：user_id 是唯一的，理论上只分组 user_id 即可，但为符合 SQL 标准，需将所有非聚合列（user_id 和 name）都列入 GROUP BY。
4. 排序
   • ORDER BY product_count DESC：按商品种类数降序排序，满足题目要求。

SELECT 
    u.user_id, 
    u.name, 
    COUNT(o.product_id) AS product_count
FROM 
    users u
LEFT JOIN 
    orders o ON u.user_id = o.user_id
GROUP BY 
    u.user_id, u.name
ORDER BY 
    product_count DESC;

是否使用 distinct

你可能会问，在count 中是否使用 distinct，COUNT(DISTINCT o.product_id) AS product_count

• Alice (user_id = 1)：购买了 product_id = 1 和 product_id = 2，虽然 product_id = 1 买了两次，但只算一次。
  • 独特产品数量：2
• Bob (user_id = 2)：购买了 product_id = 3两次，但只算一次。
  • 独特产品数量：1

分组为何加 u.name

在原始查询中，u.name 出现在 GROUP BY 中，这是 SQL 的语法要求：

• 当 SELECT 中包含聚合函数（比如 COUNT）时，任何非聚合列（比如 u.user_id 和 u.name）都必须出现在 GROUP BY 中。
• 如果 u.name 在 SELECT 中但不在 GROUP BY 中，查询会报错，因为数据库不知道如何对数据分组。

3-去除重复数据

从数据表 tableNameA 中剔除名字相同的人，并只保留每个名字对应日期最近的一条数据，如何实现。

tableNameA (
    id int not null,
    name varchar(20),
    create_time timestamp not null
)

分析与解答

思考参考

1.明确去重条件

• 去重字段：name（即按 name 分组）。
• 排序字段：create_time（按时间降序排序，取最新）。
• 保留字段：id（如果需要保留主键或其他字段）。

2. 数据量与数据库特性

• 如果数据量小，可以使用自连接或临时表。
• 如果数据量大，优先使用窗口函数（性能更好）。
• 如果数据库不支持窗口函数（如 MySQL 5.x），需用子查询或临时表。

自连接+子查询

如果你是mysql5的版本，可以使用如下方式：

SELECT t1.id, t1.name, t1.create_time
FROM tableNameA t1
JOIN (
    SELECT name, MAX(create_time) AS max_time
    FROM tableNameA
    GROUP BY name
) t2 ON t1.name = t2.name AND t1.create_time = t2.max_time;

自连接-解析

或者使用

SELECT a.id, a.name, a.create_time
FROM tableNameA a
LEFT JOIN tableNameA b
    ON a.name = b.name
    AND a.create_time < b.create_time
WHERE b.id IS NULL;

为什么使用它就能找出日期最大的时间？你是否真的了解左连接或者右链接的效果：

如果on后的条件只有 name相同，假如表数据为：

id	name	create_time
1	Alice	2023-01-01 10:00:00
2	Bob	2023-01-02 12:00:00
3	Alice	2023-01-03 15:00:00
4	Bob	2023-01-04 09:00:00
5	Alice	2024-01-05 09:00:00

下面我们一步一步来看：

SELECT a.id, a.name, a.create_time
FROM tableNameA a
LEFT JOIN tableNameA b
    ON a.name = b.name

如果执行以上sql会得到什么结果：

LEFT JOIN 的含义：

• tableNameA a 是左表，tableNameA b 是右表。
• 连接条件 ON a.name = b.name 表示将左表中每一行与右表中 name 相同的行匹配。
• 因为是 LEFT JOIN，左表中的每一行都会保留，即使右表中没有匹配的行。

自连接的特点：

• 由于是同一个表，左表中的每一行都会与右表中所有 name 相同的行（包括自身）进行匹配。
• 例如，a.id=1（name=Alice）会匹配 b.id=1、b.id=3 和 b.id=5，因为这些行的 name 都是 Alice。

结果行数：

• 每条左表记录会匹配右表中所有 name 相同的记录，因此结果中会出现重复的左表行。
• Alice 有 3 条记录，所以每条 Alice 的左表记录会生成 3 行。
• Bob 有 2 条记录，所以每条 Bob 的左表记录会生成 2 行。

a.id	a.name	a.create_time
1	Alice	2023-01-01 10:00:00
1	Alice	2023-01-01 10:00:00
1	Alice	2023-01-01 10:00:00
2	Bob	2023-01-02 12:00:00
2	Bob	2023-01-02 12:00:00
3	Alice	2023-01-03 15:00:00
3	Alice	2023-01-03 15:00:00
3	Alice	2023-01-03 15:00:00
4	Bob	2023-01-04 09:00:00
4	Bob	2023-01-04 09:00:00
5	Alice	2024-01-05 09:00:00
5	Alice	2024-01-05 09:00:00
5	Alice	2024-01-05 09:00:00

如果加上 and a.create_time < b.create_time，后，Alice的结果应该也是多条呀，为什么只有1条呢，左边id为1时，右表的id为3和5 都符合条件？

SELECT a.id, a.name, a.create_time
FROM tableNameA a
LEFT JOIN tableNameA b
    ON a.name = b.name
    AND a.create_time < b.create_time
WHERE b.id IS NULL;
-- 注意 原来的sql 除了 create_time 还有一个 b.id is null

• 逐步分析

让我们重新执行 LEFT JOIN 并加入 AND a.create_time < b.create_time 的条件，然后应用 WHERE b.id IS NULL，看看结果如何生成。

• 执行 LEFT JOIN

LEFT JOIN 会将左表（a）的每一行与右表（b）中满足 a.name = b.name AND a.create_time < b.create_time 的行匹配。

如果没有匹配的右表行，右表的字段（b.id, b.name, b.create_time）会返回 NULL。

为了更清楚，我们可以构造连接后的中间表（包括 b.id 以便观察），尽管最终查询只选择 a.id, a.name, a.create_time：

a.id	a.name	a.create_time	b.id	b.name	b.create_time
1	Alice	2023-01-01 10:00:00	3	Alice	2023-01-03 15:00:00
1	Alice	2023-01-01 10:00:00	5	Alice	2024-01-05 09:00:00
2	Bob	2023-01-02 12:00:00	4	Bob	2023-01-04 09:00:00
3	Alice	2023-01-03 15:00:00	5	Alice	2024-01-05 09:00:00
4	Bob	2023-01-04 09:00:00	NULL	NULL	NULL
5	Alice	2024-01-05 09:00:00	NULL	NULL	NULL

结果到这里，明白为什么了吧

所以，上述sql还是有缺陷的，假如存在相同的最新时间，结果可能就不止一条了。

4-查询每个部门下 工资最高的员工

给定一个员工表 employees，包含字段 employee_id、name、department_id、salary。

employees (
    employee_id INT,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2)
)

department{
    id int,
    name varchar(50)
}

查询每个部门下 工资最高的员工.

结果需按 department_id 分组，显示上述结果

分析与解答

要实现这个需求，我们需要：

1. 关联两个表：通过 employees.department_id 和 department.id 将员工表和部门表连接起来，以获取部门名称。
2. 找到每个部门的最高工资：对员工按部门分组，计算每个部门的最大工资。
3. 获取工资最高的员工：在每个部门中，找到工资等于该部门最大工资的员工（可能存在多个员工工资相同的情况）。
4. 处理细节：
   • 如果一个部门有多个员工工资并列最高，需要返回所有符合条件的员工。
   • 如果某些部门没有员工，可以选择是否包含（根据需求调整）

参考解答

SELECT 
    d.name AS department_name,
    e.name AS employee_name,
    e.salary
FROM 
    employees e
JOIN 
    department d ON e.department_id = d.id
JOIN 
    (SELECT 
         department_id, 
         MAX(salary) AS max_salary
     FROM 
         employees
     GROUP BY 
         department_id) AS max_salaries
ON 
    e.department_id = max_salaries.department_id 
    AND e.salary = max_salaries.max_salary;

注意：

在连接表时，ON 后面完全可以跟多个条件，使用 AND 或 OR 连接多个条件。这是 SQL 的标准语法，也是实际开发中常用的技巧

1. 语法允许：SQL 标准明确支持在 ON 子句中使用多个条件
2. 逻辑需求：单条件往往不能满足复杂的业务场景
3. 连接控制：更精确地控制哪些行应该匹配

扩展：ON 中的条件 vs WHERE 中的条件

特性	ON 中的条件	WHERE 中的条件
作用时机	连接过程中过滤	连接后过滤
左连接影响	不影响左表记录数	可能过滤掉左表记录
NULL处理	右表不匹配时仍保留左表	可能过滤掉未匹配记录
典型用途	控制连接匹配逻辑	最终结果集过滤

示例说明

假设有两个表：

员工表 (employees)

emp_id	name	dept_id	status
1	Alice	101	active
2	Bob	102	inactive
3	Charlie	103	active

部门表 (departments)

dept_id	dept_name	location
101	HR	New York
102	IT	Boston
103	Sales	NULL

示例1：多个条件的左连接-on

SELECT 
    e.emp_id,
    e.name,
    d.dept_name,
    d.location
FROM employees e
LEFT JOIN departments d 
    ON e.dept_id = d.dept_id 
    AND d.location IS NOT NULL  -- 额外条件
    AND e.status = 'active'     -- 另一个额外条件

结果：

emp_id	name	dept_name	location
1	Alice	HR	New York
2	Bob	NULL	NULL	← 不匹配：status='inactive'
3	Charlie	NULL	NULL	← 不匹配：location=NULL

1. Alice：匹配所有条件（dept_id匹配+location非空+status=active）
2. Bob：dept_id匹配但status='inactive' → 不匹配
3. Charlie：dept_id匹配但location=NULL → 不匹配

示例2：对比WHERE条件

SELECT 
    e.emp_id,
    e.name,
    d.dept_name,
    d.location
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location IS NOT NULL
  AND e.status = 'active'

结果：

emp_id	name	dept_name	location
1	Alice	HR	New York

所有不匹配的记录（Bob和Charlie）都被过滤掉了！

窗口函数

如果使用 MySQL 8.0 或更高版本，可以使用窗口函数来简化查询：

SELECT 
    department_name,
    employee_name,
    salary
FROM (
    SELECT 
        d.name AS department_name,
        e.name AS employee_name,
        e.salary,
        RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
    FROM 
        employees e
    JOIN 
        department d ON e.department_id = d.id
) AS ranked_employees
WHERE 
    salary_rank = 1;

窗口函数解释

• RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC)：
  • PARTITION BY e.department_id：按部门划分窗口。
  • ORDER BY e.salary DESC：在每个部门内按工资降序排序。
  • RANK()：给每个员工分配排名，工资最高的员工排名为 1。
• WHERE salary_rank = 1：只选择排名为 1 的员工（包括并列情况）。

5-动态分组和条件聚合

仍是刚刚那个表，员工表 employees，包含字段 employee_id、name、department_id、salary。

结构如下：

employees (
    employee_id INT,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2)
)

请编写SQL查询，统计每个部门的以下指标：

• 平均薪资
• 薪资高于平均的员工数
• 薪资低于平均的员工数

分析与解答

根据题意，我们是要对部分进行分组的....

步骤 1：计算每个部门的平均薪资

• 使用 GROUP BY department_id 将员工按部门分组。
• 使用 AVG(salary) 计算每个部门的平均薪资。

步骤 2：比较员工薪资与部门平均薪资

• 对于每个员工，需要将其薪资与所在部门的平均薪资进行比较。
• 可以使用子查询或窗口函数来获取每个部门的平均薪资。
• 根据比较结果，统计高于和低于平均薪资的员工数。

步骤 3：统计高于和低于平均薪资的员工数

• 使用 CASE 语句判断每个员工的薪资是否高于或低于部门平均薪资。
• 使用 SUM 聚合函数统计满足条件的员工数量。

有两种常见的实现方法：

1. 子查询：先计算每个部门的平均薪资，然后将结果与员工表连接。
2. 窗口函数（推荐，适用于 MySQL 8.0+）：直接在查询中计算部门平均薪资并进行比较，效率更高。

由于窗口函数更简洁且性能通常更好，我将优先使用窗口函数方案。如果您的数据库不支持窗口函数（如 MySQL 5.x），可以改用子查询方案。

思考参考

编写 SQL 查询时的注意事项

1.NULL 值处理：

• department_id 或 salary 可能为 NULL。
• 在计算平均薪资和比较时，需过滤掉 NULL 值（通过 WHERE 子句）或明确处理。

2.部门无员工：

• 如果某些部门没有员工，查询结果中不会包含这些部门。
• 如果需要显示所有部门（包括无员工的部门），需从部门表（如果存在）使用 LEFT JOIN，但当前只有 employees 表，因此只统计有员工的部门。

3.数据类型：

• salary 是 DECIMAL(10,2)，计算平均值时会保留小数精度。

4.性能：

• 窗口函数通常比子查询更高效，尤其在数据量较大时。
• 子查询需要多次扫描表，可能影响性能。

5.重复计算：

• 确保平均薪资只计算一次，避免在统计时重复计算导致结果错误。

子查询解决-低版本mysql

如果您的数据库不支持窗口函数（如 MySQL 5.x），可以使用以下子查询方案：

SELECT 
    e.department_id,
    AVG(e.salary) AS avg_salary,
    SUM(CASE WHEN e.salary > dept_avg.avg_salary THEN 1 ELSE 0 END) AS above_avg_count,
    SUM(CASE WHEN e.salary < dept_avg.avg_salary THEN 1 ELSE 0 END) AS below_avg_count
FROM 
    employees e
JOIN 
    (SELECT department_id, AVG(salary) AS avg_salary
     FROM employees
     WHERE department_id IS NOT NULL AND salary IS NOT NULL
     GROUP BY department_id) dept_avg
ON 
    e.department_id = dept_avg.department_id
WHERE 
    e.department_id IS NOT NULL 
    AND e.salary IS NOT NULL
GROUP BY 
    e.department_id;

查询说明

• 子查询 dept_avg 计算每个部门的平均薪资。
• 主查询通过 JOIN 将员工与其所在部门的平均薪资关联。
• 使用 CASE 和 SUM 统计高于和低于平均薪资的员工数。

窗口函数解决

以下是使用窗口函数的完整 SQL 查询：

SELECT 
    department_id,
    AVG(salary) AS avg_salary,
    SUM(CASE WHEN salary > avg_salary THEN 1 ELSE 0 END) AS above_avg_count,
    SUM(CASE WHEN salary < avg_salary THEN 1 ELSE 0 END) AS below_avg_count
FROM (
    SELECT 
        department_id,
        salary,
        AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
    FROM 
        employees
    WHERE 
        department_id IS NOT NULL 
        AND salary IS NOT NULL
) AS emp_with_avg
GROUP BY 
    department_id;

例子1-查询说明

1. 内层子查询：
   • AVG(salary) OVER (PARTITION BY department_id)：为每个员工计算所在部门的平均薪资。
   • WHERE department_id IS NOT NULL AND salary IS NOT NULL：过滤掉无效记录。
   • 结果为每个员工的 department_id、salary 和对应的部门平均薪资 avg_salary。
2. 外层查询：
   • GROUP BY department_id：按部门分组。
   • AVG(salary)：计算每个部门的平均薪资（与窗口函数结果一致，仅用于显示）。
   • SUM(CASE WHEN salary > avg_salary THEN 1 ELSE 0 END)：统计薪资高于平均值的员工数。
   • SUM(CASE WHEN salary < avg_salary THEN 1 ELSE 0 END)：统计薪资低于平均值的员工数。

结果大概可能为：

department_id	avg_salary	above_avg_count	below_avg_count
1	50000.00	3	2
2	60000.00	1	1
（具体数值取决于实际数据）

窗口函数

AVG(salary) OVER (PARTITION BY department_id) AS avg_salary，这句是什么意思，它是窗口函数吗?

是的，它是一个窗口函数（Window Function）。

1. 窗口函数的定义

窗口函数是 SQL 中的一种高级功能，允许对数据的子集（称为“窗口”）进行计算，而不将结果聚合成一行。窗口函数与常规聚合函数（如 AVG, SUM, COUNT）不同之处在于：

• 常规聚合函数会将多行聚合成一行（如 GROUP BY）。
• 窗口函数为每一行保留原始行，并为每行计算一个基于窗口的统计值。

窗口函数的一般语法是：

<函数> OVER (PARTITION BY <列名> ORDER BY <列名> [ROWS 或 RANGE 范围])

• <函数>：可以是聚合函数（如 AVG, SUM, MAX）或排名函数（如 RANK, ROW_NUMBER）。
• OVER：定义窗口的范围和计算方式。
• PARTITION BY：将数据按指定列分组，类似于 GROUP BY，但不合并行。
• ORDER BY：定义窗口内的排序（可选，影响某些函数的结果）。
• ROWS 或 RANGE：进一步限制窗口的行范围（可选）。

接下来，让我们拆解这句话：AVG(salary) OVER (PARTITION BY department_id) AS avg_salary

• AVG(salary)：

  • 这是窗口函数使用的聚合函数，表示计算 salary 列的平均值。

• OVER (...)：

  • 定义了计算的“窗口”，即对哪些行进行计算。
  • 窗口函数会为每一行生成一个结果，而不是像 GROUP BY 那样聚合成一行。

• PARTITION BY department_id：

  • 将数据按 department_id 分组（类似于 GROUP BY department_id）。
  • 每个 department_id 形成一个独立的“窗口”。
  • 在每个窗口内，AVG(salary) 只计算该 department_id 对应的所有行的 salary 平均值。

• AS avg_salary：

  • 将计算结果命名为 avg_salary，作为新列添加到查询结果中。

• 整体效果：

  • 对于 employees 表中的每一行，窗口函数会计算其所在 department_id 的所有员工的平均薪资，并将这个平均值附加到该行。
  • 结果表中，每一行仍然保留原始数据（employee_id, name, department_id, salary），并额外增加一列 avg_salary，表示该员工所在部门的平均薪资。

说的再多，不如来一个实际的例子：

employee_id	name	department_id	salary
1	Alice	1	60000
2	Bob	1	70000
3	Carol	1	50000
4	Dave	2	80000
5	Eve	2	40000

执行以下sql

SELECT 
    employee_id,
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM 
    employees;

结果会是如下：

employee_id	name	department_id	salary	avg_salary
1	Alice	1	60000	60000.00
2	Bob	1	70000	60000.00
3	Carol	1	50000	60000.00
4	Dave	2	80000	60000.00
5	Eve	2	40000	60000.00

窗口函数的使用

窗口函数是 SQL 中的一种高级功能，允许对数据的子集（称为“窗口”）进行计算，而不将结果聚合成一行。这是窗口函数的定义。

但怎么使用和记忆我想你可能还有一点懵，不要急，下面还会进一步介绍。

你可以将窗口函数分为3部分，over是中间的，相当于一个标志位。

OVER 前面的函数：表示要对数据执行的操作（即“要起的作用”），比如计算平均值（AVG）、排名（RANK）、获取前一行值（LAG）等。

OVER 后面的子句：定义了这个操作作用的数据集（即“窗口”），通过 PARTITION BY、ORDER BY 和可选的 ROWS/RANGE 来指定哪些行参与计算。

**over 前面的函数**

前面主要有3类，分别对应不同的使用场景（也可以说窗口函数分为三类）：

• 聚合类：用于统计（“我想知道某组数据的总数、平均值、最大值等”）。
  • 记忆：这些是你在 GROUP BY 中常用的函数，窗口函数只是让它们“逐行”输出。
  • 例子：AVG, SUM, MAX, MIN, COUNT。
• 排名类：用于排序或编号（“我想给数据排名，或者分配序号”）。
  • 记忆：这些函数与“竞赛”相关，想象给员工按薪资排名次。
  • 例子：ROW_NUMBER, RANK, DENSE_RANK, NTILE。
• 值类：用于访问其他行的值（“我想知道前一行、后一行或某特定行的值”）。
  • 记忆：这些函数像“窥探”前后行的数据，适合时间序列或比较场景。
  • 例子：LAG, LEAD, FIRST_VALUE, LAST_VALUE。

**OVER 后面的子句**：

• 定义了“对哪些数据做这个操作”，也就是窗口的范围。
• 包含：
  • PARTITION BY：将数据分成组（类似 GROUP BY，但不合并行），每个组是一个独立的窗口。
  • ORDER BY（可选）：指定窗口内行的顺序，影响排名函数或值函数的结果。
  • ROWS 或 RANGE（可选）：进一步限制窗口内的行，比如只计算前几行或某个值范围。
• 记忆：它是“对谁做”的部分，定义了数据的子集。

下面是一个简单的例子：

employee_id	name	department_id	salary
1	Alice	1	60000
2	Bob	1	70000
3	Carol	1	50000
4	Dave	2	80000

SELECT 
    employee_id,
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

结果

employee_id	name	department_id	salary	avg_salary
1	Alice	1	60000	60000.00
2	Bob	1	70000	60000.00
3	Carol	1	50000	60000.00
4	Dave	2	80000	80000.00

注意事项

在编写和使用窗口函数时，需要注意以下几点：

1. 性能：
   • 窗口函数通常比子查询更高效，因为它们只需扫描一次数据。
   • 但在数据量非常大时，PARTITION BY 可能导致内存占用较高。
2. NULL 值：
   • 如果 salary 或 department_id 包含 NULL，AVG 会忽略 NULL 的 salary 值，但 PARTITION BY 会将 NULL 视为一个单独的分组。
   • 必要时，使用 WHERE department_id IS NOT NULL 过滤。
3. 窗口范围：
   • 当前查询没有 ORDER BY 或 ROWS/RANGE 子句，因此 AVG 是对整个分区（每个 department_id）计算。
   • 如果加上 ORDER BY，可以实现累积平均等更复杂计算。
4. MySQL 版本：
   • 窗口函数仅在 MySQL 8.0 及以上版本支持。如果使用 MySQL 5.x，需要改用子查询。