引言：从 “提取数据” 到 “洞察价值”，SQL 进阶之路
在掌握了基础查询与多表关联后，你是否曾遇到这样的挑战：如何按部门统计平均薪资？怎样找出每个岗位薪资最高的员工？或者如何计算销售额的月度环比增长率？这些问题的核心，在于从 “简单提取数据” 升级为 “深度分析数据”
本文作为 SQL 查询系列的下篇，将聚焦分组查询、子查询、窗口函数三大核心技术，带你突破数据处理的瓶颈。你将学习如何用GROUP BY实现 “按类别汇总”，用子查询解决 “嵌套逻辑” 问题，更能掌握窗口函数的 “黑科技”，轻松实现排名、累计值、移动平均等复杂分析。
目录
六、分组查询和聚合函数
一、统计函数（聚合函数）
统计函数用于对数据表中的数据进行汇总计算，返回单一结果。常见统计函数包括：
COUNT(字段)：统计非NULL值的数量（如统计人数、记录数）。
- 示例：
SELECT COUNT(EMPNO) 人数 FROM emp;（统计员工总数）AVG(字段)：计算数值型字段的平均值（如平均工资）。
- 示例：
SELECT AVG(SAL) 平均工资 FROM emp;（计算所有员工平均工资）。其他常用函数：SUM(字段)（求和）、MAX(字段)（最大值）、MIN(字段)（最小值）等。
特点：
- 可直接作用于单表，无需分组时，返回整个表的汇总结果。
- 常与分组查询结合，对每个分组单独计算统计结果。
二、分组查询(GROUP BY)
分组查询用于将表中的数据按指定字段分组，对每个分组单独应用统计函数，实现 “按类别汇总” 的需求。
1. 基本语法
SELECT 分组字段1, 分组字段2, 统计函数(字段)
FROM 表名
[WHERE 条件] -- 分组前筛选原始数据（不涉及统计结果）
GROUP BY 分组字段1, 分组字段2 -- 按字段分组，字段需出现在SELECT中
[HAVING 统计条件] -- 分组后筛选统计结果（需使用统计函数）
[ORDER BY 排序字段]; -- 对最终结果排序2. 关键要点
WHERE vs HAVING：
WHERE：在分组之前筛选数据，不能使用统计函数（如WHERE AVG(SAL) > 2000 错误）。
HAVING：在分组之后筛选统计结果，可使用统计函数（如HAVING AVG(SAL) > 2000 正确）。
分组字段：GROUP BY 后的字段必须在 SELECT 中出现（除非使用统计函数覆盖），确保每个分组的唯一性。
3. 示例解析
按岗位分组统计人数：
SELECT JOB, COUNT(EMPNO) 人数 FROM emp GROUP BY JOB;# 4.查询每个岗位的详细信息，包含平均薪资
-- 查询每个岗位的详细信息，包含平均薪资
SELECT
emp.job 岗位,
COUNT(emp.empno) 员工人数,
AVG(emp.sal) 平均薪资,
MIN(emp.sal) 最低薪资,
MAX(emp.sal) 最高薪资,
SUM(emp.sal) 薪资总和
FROM
emp
GROUP BY
emp.job
ORDER BY
平均薪资 DESC;# 5.查询每个部门的详细信息，包含平均薪资
SELECT
d.deptno 部门编号, -- 部门编号（来自 dept 表）
d.dname 部门名称, -- 部门名称（来自 dept 表）
COUNT(e.empno) 部门人数,
AVG(e.sal) 平均薪资
FROM
emp e
LEFT JOIN
dept d
ON e.deptno = d.deptno -- 连接员工表和部门表
GROUP BY
d.deptno; -- 按部门分组（确保部门编号和名称唯一）结合右连接查询，按部门分组并筛选平均薪资高于 2000 的部门：
# 6.#查询平均薪资高于2000的部门信息
SELECT
d.deptno 部门编号, -- 部门编号（来自 dept 表）
d.dname 部门名称, -- 部门名称（来自 dept 表）
COUNT(e.empno) 部门人数,
AVG(e.sal) 平均薪资
FROM
emp e
RIGHT JOIN
dept d
USING
(DEPTNO) -- 连接员工表和部门表
GROUP BY
d.deptno -- 按部门分组（确保部门编号和名称唯一）
HAVING
avg(e.SAL) > 2000
ORDER BY 平均薪资 DESC;三、单行函数
单行函数是对表中的每一行数据单独处理，返回与原表行数相同的结果。常见类型包括：
字符函数：UPPER(字段)（转大写）、LOWER(字段)（转小写）、LENGTH(字段)（长度）等。
示例：SELECT UPPER(ENAME) FROM emp;（将员工姓名转为大写）。
数值函数：ROUND(字段, 小数位)（四舍五入）、TRUNC(字段, 小数位)（截断）等。
示例：SELECT ROUND(SAL, 2) FROM emp;（工资保留两位小数）。
日期函数：SYSDATE()（当前系统时间）、TO_DATE(字符串, 格式)（字符串转日期）等。
示例：SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD') FROM dual;（将字符串转为日期）。
转换函数：TO_CHAR(字段, 格式)（日期 / 数值转字符串）、TO_NUMBER(字符串)（字符串转数值）等。
示例：SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;（当前日期转为字符串）。
特点：
处理粒度为 “单行”，不改变表的行数，仅对每行数据进行转换或计算。
可与统计函数、分组查询混合使用，实现更复杂的业务逻辑。
SELECT
deptno 部门编号,
UPPER(ename) 部门名称大写, -- 单行函数：部门名称转大写
COUNT(empno) 部门人数, -- 统计函数：按部门统计人数
AVG(sal) 平均薪资 -- 统计函数：按部门计算平均工资
FROM emp
GROUP BY deptno, ename; -- 分组查询：按部门编号和名称分组
具体来说，当你使用 GROUP BY 进行分组查询时，SELECT 后面只能出现两种类型的列：
出现在 GROUP BY 子句中的列
使用聚合函数（如 SUM(), COUNT(), AVG(), MAX(), MIN() 等）处理过的列四、统计函数与分组查询与单行函数的关系
统计函数 vs 单行函数：
统计函数：对一组数据（可能是全表或分组）汇总计算，返回单一结果。
单行函数：对每行数据单独处理，返回与原表行数相同的结果。
分组查询的桥梁作用：
分组查询通过 GROUP BY 将数据划分为多个子集，每个子集可应用统计函数生成汇总结果。
单行函数可在分组前对原始数据进行转换（如日期格式化、字符串处理），或在分组后对统计结果进行二次处理。
例如：
不分组：AVG(SAL) 计算所有员工的平均工资。
# 所有人数
SELECT COUNT(EMPNO) 人数
FROM emp;
# 所有员工平均工资
SELECT AVG(SAL) 平均工资
FROM emp;按部门分组：GROUP BY deptno + AVG(SAL) 计算每个部门的平均工资。
# 统计各岗位平均工资
SELECT JOB 岗位,AVG(SAL) 平均工资
FROM emp
GROUP BY JOB;三者同时使用
SELECT
deptno 部门编号,
UPPER(dname) 部门名称大写, -- 单行函数：部门名称转大写
COUNT(empno) 部门人数, -- 统计函数：按部门统计人数
AVG(sal) 平均薪资 -- 统计函数：按部门计算平均工资
FROM emp
GROUP BY deptno, dname; -- 分组查询：按部门编号和名称分组SELECT
deptno 部门编号,
UPPER(ename) 部门名称大写, -- 单行函数：部门名称转大写
COUNT(empno) 部门人数, -- 统计函数：按部门统计人数
AVG(sal) 平均薪资 -- 统计函数：按部门计算平均工资
FROM emp
GROUP BY deptno, ename; -- 分组查询：按部门编号和名称分组
具体来说，当你使用 GROUP BY 进行分组查询时，SELECT 后面只能出现两种类型的列：
出现在 GROUP BY 子句中的列
使用聚合函数（如 SUM(), COUNT(), AVG(), MAX(), MIN() 等）处理过的列七、WHERE 子查询与 FROM 子查询
(一)、WHERE 子查询
定义：子查询出现在 WHERE 或 HAVING 子句中，作为条件表达式的一部分，用于筛选主查询的数据。
1. 语法特点
子查询结果需能作为条件值或条件列表，常见形式：
单行子查询：返回单一值，用于 =、>、< 等比较运算。
多行子查询：返回多个值，用于 IN、ANY、ALL 等逻辑运算。
执行顺序：先执行子查询，再将结果作为条件筛选主查询数据。
2. 示例解析
单行子查询（比较运算）：
-- 查询比SMITH工资高的员工信息
SELECT *
FROM emp
WHERE SAL > (SELECT SAL FROM emp WHERE ENAME = 'SMITH')
ORDER BY SAL DESC;子查询 (SELECT SAL FROM emp WHERE ENAME = 'SMITH') 返回 SMITH 的工资（单行值）。
主查询筛选工资大于该值的员工。
多行子查询（IN 或 ANY）：
-- 查询与销售岗位薪资相同的员工信息
SELECT *
FROM emp
WHERE SAL IN (SELECT SAL FROM emp WHERE JOB = 'SALESMAN');子查询返回所有销售岗位的薪资（多行值）。
主查询筛选薪资在该列表中的员工。
聚合函数子查询：
-- 查询比平均工资高的员工信息
SELECT *
FROM emp
WHERE SAL > (SELECT AVG(SAL) FROM emp);3. 关键字扩展（ANY/ALL）
= ANY：
等价于 IN，匹配列表中任意一个值。
SELECT *
FROM emp
WHERE SAL = ANY (SELECT SAL FROM emp WHERE JOB = 'SALESMAN');< ANY：
小于列表中的最大值。
SELECT *
FROM emp
WHERE SAL < ANY (SELECT SAL FROM emp WHERE JOB = 'SALESMAN');> ANY：
比最小值大的数据
> ALL：
大于列表中的最大值。
< ALL：
小于列表中的最小值。
(二)、FROM 子查询
定义：子查询出现在 FROM 子句中，作为主查询的 “临时表”，需为子查询指定别名。
1. 语法特点
子查询结果作为数据源，主查询对其进行二次筛选或关联。
子查询可预先过滤数据，减少主查询的处理量。
执行顺序：先执行子查询生成临时表，再执行主查询。
2. 示例解析
简单子查询作为临时表：
-- 与WHERE子查询示例1结果相同，但语法结构不同
SELECT *
FROM (
SELECT *
FROM emp
WHERE SAL > (SELECT SAL FROM emp WHERE ENAME = 'SMITH')
) AS high_sal_emp -- 必须指定别名
ORDER BY SAL DESC;子查询生成 “工资高于 SMITH 的员工” 临时表。
主查询对临时表按工资降序排序。
预先过滤数据：
SELECT *
FROM (
SELECT empno, ename, sal, deptno
FROM emp
WHERE job = 'SALESMAN'
) AS salesmen
WHERE sal >= 1500;子查询预先筛选出销售岗位的员工。
主查询在临时表基础上，进一步筛选薪资≥1500 的员工。
3. 优势
逻辑分层：将复杂查询拆分为 “数据准备层”（子查询）和 “结果处理层”（主查询），便于维护。
性能优化：子查询可提前过滤冗余数据，减少主查询的计算量。
(三)、WHERE 子查询 vs FROM 子查询
维度 |
WHERE 子查询 |
FROM 子查询 |
位置 |
出现在WHERE/HAVING子句中 |
出现在FROM子句中 |
作用 |
作为条件筛选主查询数据 |
作为数据源（临时表）供主查询使用 |
执行顺序 |
先执行子查询，再执行主查询 |
先执行子查询生成临时表，再执行主查询 |
子查询结果 |
需为单一值或值列表（匹配条件） |
需为完整数据集（可含多字段、多行） |
别名要求 |
无需别名 |
必须为子查询指定别名（临时表名） |
典型场景 |
基于子查询结果的条件筛选（如 “比 SMITH 工资高”） |
复杂数据预处理（如 “先筛选销售岗位，再处理薪资”） |
四、总结
WHERE 子查询：适用于将子查询结果作为条件，直接筛选主查询数据，语法简洁，适合简单条件判断。
FROM 子查询：适用于复杂数据预处理，通过临时表分层处理逻辑，便于维护和性能优化。
两者可结合使用，实现更复杂的业务需求（如子查询嵌套、多表关联等）。
八、SELECT查询
定义：SELECT 是 SQL 中用于从表中提取数据的基础关键字，所有查询操作均以 SELECT 开头，决定返回哪些字段及如何展示。
(一)、SELECT 查询的基础用法
字段选择规则
可选择表中所有字段：用 * 表示（如 SELECT * FROM emp;），适合快速查看全表数据，但效率较低（不推荐大数据量场景）。
可选择指定字段：用逗号分隔字段名（如 SELECT EMPNO, ENAME, SAL FROM emp;），仅返回所需数据，减少资源消耗。
支持字段去重：在字段前加 DISTINCT（如 SELECT DISTINCT JOB FROM emp;），仅保留该字段的唯一值（DISTINCT 仅对紧跟的第一个字段生效）。
字段别名设置
为字段指定可读性更强的别名（尤其适合中文展示），语法：字段名 别名 或 字段名 AS 别名（AS 可省略）。
示例：SELECT ENAME 姓名, JOB 职位, SAL 薪资 FROM emp;
函数与表达式支持
可在 SELECT 后直接使用单行函数处理字段（如字符转换、数值计算等）：
SELECT UPPER(ENAME) 姓名大写, ROUND(SAL, 2) 薪资保留两位小数 FROM emp;可在 SELECT 后使用统计函数（聚合函数）进行汇总计算（常与 GROUP BY 结合）：
SELECT JOB, COUNT(EMPNO) 人数, AVG(SAL) 平均薪资 FROM emp GROUP BY JOB;可嵌套子查询作为字段值，返回与主查询行数一致的结果
SELECT ENAME, SAL, (SELECT MAX(SAL) FROM emp) 最高工资 FROM emp;SELECT子查询
定义：SELECT子查询是嵌套在SELECT子句中的子查询，作为主查询返回结果的一个字段值，其结果与主查询的行数保持一致（每行返回一个对应值）。
一、语法特点
位置：出现在SELECT子句中，作为一个 “虚拟字段” 存在。
结果要求：子查询需返回单行单列的值（即单个数值或字符串），确保与主查询的每行记录一一对应。
执行逻辑：主查询每读取一行数据，子查询会执行一次，返回对应的值作为该字段的结果。
二、示例解析
SELECT
ENAME 姓名,
SAL 薪资,
(SELECT MAX(SAL) FROM emp) AS 最高工资 -- SELECT子查询：返回全表最高工资
FROM emp;子查询(SELECT MAX(SAL) FROM emp)独立计算全表最高工资（单行单列结果）。
主查询返回每位员工的姓名、薪资，同时将子查询结果作为 “最高工资” 字段，与每行员工信息对应。
三、使用场景
关联单行数据：获取与主查询每行记录相关的单行参考值（如对比个人薪资与全表最高薪资）。
简化多步查询：无需单独执行子查询再手动关联结果，直接在SELECT中嵌套实现。
四、注意事项
子查询必须返回单行单列结果，否则会报错（如返回多行时需配合LIMIT 1等限制）。
避免在大数据量场景中过度使用，因每行执行一次子查询可能导致性能下降（可通过关联查询优化）。
总结：SELECT子查询通过在字段列表中嵌套单行子查询，为每条主查询记录附加一个动态计算的参考值，适用于需对比或补充单行关联数据的场景。
九、分页查询
(一)、分页查询的必要性
性能优化：当表数据量庞大时（如百万级记录），一次性查询所有数据会导致：
数据库查询耗时久，占用大量内存。
网络传输数据量大，前端渲染压力大。分页查询可按需获取数据，显著提升效率。
用户体验：网页或应用中，分页展示数据更符合用户浏览习惯（如 “下一页”“上一页” 按钮），避免信息过载。
(二)、分页查询语法（以 MySQL 为例）
关键字：LIMIT n, m
n：起始数据的索引（从 0 开始）。
m：每页显示的条数。
语法规则
SELECT 字段 FROM 表名
[WHERE 条件]
[ORDER BY 排序字段]
LIMIT n, m;示例
1. 查询首页数据（前 10 条）：
SELECT * FROM emp LIMIT 0, 10; -- 或简写为 LIMIT 10- n=0 表示从第一条数据开始（索引 0）。
- m=10 表示每页显示 10 条。
2. 查询第二页数据（第 11-20 条）：
SELECT * FROM emp LIMIT 10, 10;- n=10 表示从第 11 条数据开始（索引 10）。
- m=10 表示每页显示 10 条。
(三)、分页查询与其他语法的结合
与排序结合：
分页前需对数据排序，确保每页数据的顺序一致。
SELECT * FROM emp
ORDER BY SAL DESC -- 按工资降序排序
LIMIT 10, 10; -- 查询第二页数据与筛选条件结合：
先筛选数据，再分页展示。
SELECT * FROM emp
WHERE DEPTNO = 10 -- 仅查询部门10的员工
LIMIT 0, 5; -- 每页显示5条与连接查询结合：
多表关联后分页展示结果。
SELECT
e.ENAME, d.DNAME
FROM
emp e
LEFT JOIN dept d
ON e.DEPTNO = d.DEPTNO
LIMIT 0, 5;(四)、分页查询的注意事项
数据库兼容性：
MySQL 使用 LIMIT n, m。
Oracle 使用 ROWNUM 或 OFFSET...FETCH（如 OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY）。
SQL Server 使用 OFFSET...FETCH（与 Oracle 类似）。
索引优化：分页查询时，若涉及排序字段，需确保该字段有索引，否则大量数据排序会导致性能下降。
动态计算页码：在实际应用中，页码需根据总记录数和每页条数动态计算，例如：
总记录数：SELECT COUNT(*) FROM emp;
总页数：CEIL(总记录数 / 每页条数)
SELECT COUNT(*) FROM emp;
SELECT CEIL((SELECT COUNT(*) FROM emp) / 10);十、函数整理
窗口函数
窗口函数是现代数据分析中处理复杂计算、排名、移动平均、累计值、分区内比较等任务的利器，是数据分析师日常工作中频繁使用的工具。
为什么窗口函数对数据分析岗位至关重要？
处理复杂排名和分位数： 计算销售排名、部门内薪资排名、成绩百分位等（ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK(), CUME_DIST()）。
计算趋势和移动指标： 计算滚动平均销售额、移动总和、环比/同比变化等（SUM() OVER(...), AVG() OVER(...), 结合 ROWS/RANGE 子句）。
访问前后行数据： 分析用户行为序列（如上一步/下一步操作）、计算与前一行的差值或比率（LAG(), LEAD()）。
分区内聚合而不折叠行： 计算每个员工薪资占其部门总薪资的比例、计算每个客户订单总额的同时保留订单明细（SUM() OVER(PARTITION BY ...)）。
累积计算： 计算年初至今（YTD）销售额、累计用户数等（SUM() OVER(ORDER BY ... ROWS UNBOUNDED PRECEDING)）。
高效处理“Top N per Group”问题： 找出每个部门薪资最高的前3名员工（结合 ROW_NUMBER() 或 RANK() 和子查询/CTE）。
功能：
对数据的指定分区（窗口） 进行跨行计算，不折叠结果集（保留原始行），实现排名、累积、移动平均等复杂分析。
与 GROUP BY 关键区别：
维度 |
GROUP BY （分组聚合） |
窗口函数 |
结果行数 |
折叠为分组数（行数减少） |
保留原表行数（行数不变） |
计算粒度 |
每组返回一个汇总值 |
每行返回基于窗口的独立计算结果 |
典型场景 |
部门平均工资、岗位人数统计 |
部门内薪资排名、累计销售额分析 |
1. 基础语法框架
SELECT
字段1,
字段2,
窗口函数() OVER (
[PARTITION BY 分区字段] -- 将数据划分为多个窗口（类似分组）
[ORDER BY 排序字段] -- 窗口内数据排序（影响排名、累积计算）
[ROWS/RANGE 窗口帧] -- 定义计算范围（如 "当前行前3行"）
) AS 别名
FROM 表名; 2. 常用窗口函数类型与示例
1）排名函数
函数 |
功能说明 |
示例场景 |
ROW_NUMBER() |
为分区内每行生成唯一连续序号 |
按部门给员工薪资排名（无并列） |
RANK() |
并列时跳过后续序号（1,1,3） |
销售业绩排名（允许名次并列） |
DENSE_RANK() |
并列时不跳号（1,1,2） |
学生成绩等级排名（紧密排序） |
示例：查询每个部门内员工的薪资排名（允许并列）
SELECT
DEPTNO 部门编号,
ENAME 员工姓名,
SAL 薪资,
RANK() OVER (
PARTITION BY DEPTNO -- 按部门分区
ORDER BY SAL DESC -- 薪资降序排序
) AS 部门内薪资排名
FROM emp; 结果示例：
部门编号 |
员工姓名 |
薪资 |
部门内薪资排名 |
|
10 |
KING |
5000 |
1 |
|
10 |
CLARK |
2450 |
2 |
|
20 |
SCOTT |
3000 |
1 |
|
20 |
FORD |
3000 |
1 |
-- 并列第一 |
20 |
ADAMS |
1100 |
3 |
-- RANK() 跳过2 |
2）聚合函数 + 窗口函数
功能：在分区内计算聚合值（如累加、移动平均），不折叠行。
常用函数：
SUM(字段) OVER (...) -- 累计求和
AVG(字段) OVER (...) -- 移动平均
MAX/MIN(字段) OVER (...) 示例1：计算每位员工的累计薪资（按入职顺序累加）
SELECT
ENAME,
HIREDATE,
SAL,
SUM(SAL) OVER (
ORDER BY HIREDATE -- 按入职日期排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 从首行到当前行
) AS 累计薪资
FROM emp; 3）取值函数
函数 |
功能说明 |
LAG(字段, N) |
获取当前行前N行的数据 |
LEAD(字段, N) |
获取当前行后N行的数据 |
FIRST_VALUE(字段) |
返回窗口内第一行的值 |
LAST_VALUE(字段) |
返回窗口内最后一行的值 |
示例：分析每月销售额环比增长率
SELECT
sale_month,
sales,
LAG(sales, 1) OVER (ORDER BY sale_month) AS 上月销售额,
(sales - LAG(sales, 1) OVER (ORDER BY sale_month)) / LAG(sales, 1) OVER (ORDER BY sale_month) AS 环比增长率
FROM sales_table; 3. 窗口帧控制（ROWS vs RANGE）
关键字 |
说明 |
示例 |
ROWS |
物理行 范围（推荐使用） |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING （当前行前后各1行） |
RANGE |
逻辑值 范围（易混淆） |
RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING （值在±100内） |
常用帧范围：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 从分区开始到当前行（累计算）
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING -- 前3行到后1行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- 当前行到分区结束 4. 核心特点总结
- 不折叠结果：保留原表所有行，新增计算列，适合明细级分析报告。
- 分层计算：通过 PARTITION BY 实现“组内分析”（如部门内排名）。
- 动态范围：ORDER BY + 窗口帧 支持时间序列分析（移动平均、累计值）。
- 性能优化：比自连接/子查询更高效处理复杂分析（如 Top N 问题）。
- 面试重点：数据分析岗位必考，用于解决：
- 排名问题（ROW_NUMBER(), RANK()）
- 趋势分析（LAG()/LEAD(), 移动平均）
- 占比计算（SUM() OVER(PARTITION BY)）