专栏名称: CDA数据分析师
CDA数据分析师品牌官方微信，开放、创新、分享。
目录
相关文章推荐
艺恩数据  ·  【艺恩报告】2025家电“国补”焕新社媒研究 ·  昨天  
CDA数据分析师  ·  Deepseek来袭，数据分析师会失业吗？ ·  2 天前  
数据派THU  ·  人工智能是不是走错了方向？ ·  5 天前  
数据派THU  ·  十宗「最」！2024 年 AI ... ·  2 天前  
数据派THU  ·  【博士论文】学习视觉-语言表示以实现多模态理解 ·  6 天前  
51好读  ›  专栏  ›  CDA数据分析师

【干货】2小时用AI完成的SQL教程也太赞了吧，不推荐deepseek

CDA数据分析师  · 公众号  · 大数据  · 2025-02-19 09:00

正文

作者：数据星爷，CDA特约作者
上市公司资深分析师/微软认证Excel专家


SQL查询是数据分析工作的基础，也是CDA数据分析师一级的核心考点，人工智能时代，AI能为我们节省多少工作量？ 本来想用deepseek部署 ，被卡出三界外，不在五行中，后来选择了通义灵码。


环境

  • AI环境 ：

    • VScode+通义灵码

  • 练习网站 ：

    • https://sqlfiddle.com/postgresql/online-compiler

1. 基础概念

1.1  数据库和表的概念

数据库 ：数据库是一个有组织的数据集合，通常以电子表格的形式存储。它由多个表组成，每个表代表一种特定类型的数据集合。

表 ： 表是数据库中数据的基本存储单位，类似于Excel中的工作表。表由行（记录）和列（字段）构成。每一行代表一条记录，每一列代表一个属性。

例如，在一个电商数据库中，可能会有以下几张表：

  • users 表：存储用户信息，如用户ID、用户名、邮箱等。

  • orders 表：存储订单信息，如订单ID、用户ID、订单日期等。

  • products 表：存储商品信息，如商品ID、名称、价格等


1.2 SQL语言的基本结构

SQL（Structured Query Language，结构化查询语言）是一种用于 管理和操作 关系型数据库的标准语言。它的基本结构包括以下几个部分：

  • SELECT ：用于从数据库中检索数据。

  • FROM ：指定要查询的表。

  • WHERE ：用于过滤数据，只返回满足条件的记录。

  • GROUP BY ：用于将结果集按一个或多个列进行分组。

  • HAVING ：用于在分组后进一步过滤数据。

  • ORDER BY ：用于对结果集进行排序。

  • LIMIT ：用于限制返回的结果数量。


1.3 示例

假设我们有一个名为 users 的表，包含以下字段：

  • id (用户ID)

  • name (用户名)

  • email (用户邮箱)

我们可以编写一个简单的SQL查询来获取所有用户的姓名和邮箱： 

SELECT name, email FROM users;

如果我们只想获取名字中包含 "张" 的用户，可以使用 WHERE 子句： 

SELECT name, emailFROM usersWHERE name LIKE '%张%';

1.4 总结

通过理解数据库和表的概念以及SQL语言的基本结构，你可以开始构建简单的查询语句来检索和操作数据。这是学习SQL的基础，后续我们将在此基础上深入学习更复杂的查询和操作 。

如果大家对SQL比较熟悉，学得比较好，想了解具体的代码的掌握情况，可以在 CDA认证 小程序上刷模拟题测试效果。

图片


2. 数据检索

数据检索是SQL中最常用的操作之一，它允许你从数据库中提取所需的数据。我们将详细讲解如何使用 SELECT 语句及其相关子句来实现各种数据检索操作。

2.1 使用 WITH 子句创建临时表

首先，我们创建一个包含10条员工数据的临时表 employees 。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS




    
 department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)

2.2  使用 SELECT 语句从单个表中检索数据

示例

要获取所有员工的姓名和部门： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, departmentFROM employees;

结果：

name

department

张三

销售部

李四

市场部

王五

销售部

赵六

技术部

孙七

市场部

周八

技术部

吴九

销售部

郑十

市场部

钱十一

技术部

王十二

销售部

2.3 使用 WHERE 子句过滤数据

示例

要获取工资大于5000的员工信息： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesWHERE salary > 5000;

结果

name

salary

张三

5000

李四

6000

王五

5500

赵六

7000

孙七

6500

周八

7500

郑十

5000

钱十一

8000

2.4 使用 ORDER BY 排序结果集

示例

要按工资从高到低排序员工信息： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesORDER BY salary DESC;

结果

name

salary

钱十一

8000

周八

7500

赵六

7000

孙七

6500

李四

6000

王五

5500

张三

5000

郑十

5000

吴九

4500

王十二

4000

2.5 使用 LIMIT 限制返回行数

示例

要获取前5名最高工资的员工信息，并按工资降序排序： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 




    
AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesORDER BY salary DESCLIMIT 5; 
结果

name

salary

钱十一

8000

周八

7500

赵六

7000

孙七

6500

李四

6000

2.6 组合使用多个子句

示例

要获取属于 "销售部" 的前3名最高工资的员工信息，并按工资降序排序： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION




    
 ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesWHERE department = '销售部'ORDER BY salary DESCLIMIT 3;

结果

name

salary

王五

5500

张三

5000

吴九

4500

2.7 总结

通过上述示例，你可以看到如何使用 WITH 子句创建一个临时表，并在此基础上进行各种数据检索操作。这些示例涵盖了 SELECT 、 WHERE 、 ORDER BY 和 LIMIT 的基本用法，帮助你更好地理解和验证查询结果。

  • 使用 SELECT 语句从单个表中检索数据

  • 使用 WHERE 子句过滤数据

  • 使用 ORDER BY 排序结果集

  • 使用 LIMIT 限制返回行数

3. 聚合与分组

使用 COUNT() , SUM() , AVG() , MAX() , MIN() 等聚合函数

使用 GROUP BY 对数据进行分组

3.1 使用 WITH 子句创建临时表

首先，我们创建一个包含10条员工数据的临时表 employees 。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一'




    
, '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)

3.2 聚合函数

聚合函数用于对一组值执行计算并返回单个值。常用的聚合函数包括：

  • COUNT() ：计算行数。

  • SUM() ：计算总和。

  • AVG() ：计算平均值。

  • MAX() ：返回最大值。

  • MIN() ：返回最小值。

示例- count

计算总员工数： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT COUNT(*) AS total_employeesFROM employees;
结果：

total_employees

10

示例-sum

计算所有员工的总薪水 ： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 




    
UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT SUM(salary) AS total_salaryFROM employees;

结果：

total_salary

59000

示例-avg

计算所有员工的平均薪水： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT AVG(salary) AS average_salaryFROM employees;

结果：

average_salary

5900.00

示例-max

返回最高薪水： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL




    
    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT MAX(salary) AS max_salaryFROM employees;

结果：

max_salary

8000

示例-min

返回最低薪水： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT MIN(salary) AS min_salaryFROM employees;

结果：

min_salary

4000

3.3  使用 GROUP BY 进行分组

GROUP BY 子句用于将结果集按一个或多个列进行分组，通常与聚合函数一起使用。

示例-count

按部门分组，计算每个部门的员工数 ： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL




    
    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT department, COUNT(*) AS employee_countFROM employeesGROUP BY department;

结果：

department

employee_count

销售部

4

市场部

3

技术部

3

示例-sum

  • 按部门分组，计算每个部门的总薪水： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)




    
SELECT department, SUM(salary) AS total_salaryFROM employeesGROUP BY department;

结果：

department

total_salary

销售部

19000

市场部

17500

技术部

22500

示例-avg

  • 按部门分组，计算每个部门的平均薪水： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT department, AVG(salary) AS average_salaryFROM employeesGROUP BY department;

结果：

department

average_salary

销售部

4750

市场部

5833.3

技术部

7500

示例-max-min

  • 按部门分组，返回每个部门的最高和最低薪水： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION 




    
ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT department, MAX(salary) AS max_salary, MIN(salary) AS min_salaryFROM employeesGROUP BY department;

结果：

department

max_salary

min_salary

销售部

5500

4000

市场部

6500

5000

技术部

8000

7000

3.4 总结

通过上述示例，你可以看到如何使用聚合函数和 GROUP BY 子句来对数据进行分组和计算。这些示例涵盖了 COUNT() 、 SUM() 、 AVG() 、 MAX() 和 MIN() 的基本用法，并结合 GROUP BY 进行分组操作。

如果大家对SQL比较熟悉，学得比较好，想了解具体的代码的掌握情况，可以在 CDA认证 小程序上刷模拟题测试效果。

图片


4. 多表操作

我们将详细讲解SQL中的多表操作，包括内连接（ INNER JOIN ）、左连接（ LEFT JOIN ）、右连接（ RIGHT JOIN ）和全外连接（ FULL OUTER JOIN ）。我们将使用 WITH 子句创建两个临时表 employees 和 departments ，然后进行各种多表查询。

4.1 使用 WITH 子句创建临时表

首先，我们创建两个临时表 employees 和 departments 。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    




    
SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')

4.2  内连接（ INNER JOIN ）

内连接返回两个表中满足连接条件的所有行。

示例

要获取每个员工及其所属部门的名称： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部'




    
 UNION ALL    SELECT 3, '技术部')SELECT e.name, d.department_name, e.salaryFROM employees eINNER JOIN departments d ON e.department_id = d.id;

结果

name

department_name

salary

张三

销售部

5000

李四

市场部

6000

王五

销售部

5500

赵六

技术部

7000

孙七

市场部

6500

周八

技术部

7500

吴九

销售部

4500

郑十

市场部

5000

钱十一

技术部

8000

王十二

销售部

4000

4.3 左连接（ LEFT JOIN ）

左连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则结果为 NULL 。

示例

要获取所有员工及其所属部门的名称，即使某些员工没有部门： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    




    
SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000 UNION ALL    SELECT 11, '无部门员工', NULL, 4000 -- 添加一个没有部门的员工),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')SELECT e.name, d.department_name, e.salaryFROM employees eLEFT JOIN departments d ON e.department_id = d.id;

结果

name

department_name

salary

张三

销售部

5000

李四

市场部

6000

王五

销售部

5500

赵六

技术部

7000

孙七

市场部

6500

周八

技术部

7500

吴九

销售部

4500

郑十

市场部

5000

钱十一

技术部

8000

王十二

销售部

4000

无部门员工

NULL

4000

4.4 右连接（ RIGHT JOIN ）

右连接返回右表中的所有行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则结果为 NULL 。

示例

要获取所有部门及其所属员工的名称，即使某些部门没有员工： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 




    
1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部' UNION ALL    SELECT 4, '人力资源部' -- 添加一个没有员工的部门)SELECT e.name, d.department_name, e.salaryFROM employees eRIGHT JOIN departments d ON e.department_id = d.id;

结果

name

department_name

salary

张三

销售部

5000

李四

市场部

6000

王五

销售部

5500

赵六

技术部

7000

孙七

市场部

6500

周八

技术部

7500

吴九

销售部

4500

郑十

市场部

5000

钱十一

技术部

8000

王十二

销售部

4000

NULL

人力资源部

NULL

4.5 全外连接（ FULL OUTER JOIN ）

全外连接返回两个表中的所有行，如果某个表中没有匹配的行，则结果为 NULL 。需要注意的是，并非所有数据库系统都支持 FULL OUTER JOIN ，例如 MySQL 不支持，但可以通过 UNION 实现类似效果。

示例

要获取所有员工及其所属部门的名称，以及所有部门及其所属员工的名称，即使某些员工或部门没有匹配： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部' UNION ALL    SELECT 4, '人力资源部' -- 添加一个没有员工的部门)SELECT e.name, d.department_name, e.salaryFROM employees eFULL OUTER JOIN departments d ON e.department_id = d.id;

结果

name

department_name

salary

张三

销售部

5000

李四

市场部

6000

王五

销售部

5500

赵六

技术部

7000

孙七

市场部

6500

周八

技术部

7500

吴九

销售部

4500

郑十

市场部

5000

钱十一

技术部

8000

王十二

销售部

4000

NULL

人力资源部

NULL

4.6 总结

通过上述示例，你可以看到如何使用内连接、左连接、右连接和全外连接来操作多个表。这些示例涵盖了 INNER JOIN 、 LEFT JOIN 、 RIGHT JOIN 和 FULL OUTER JOIN 的基本用法，并结合 WITH 子句创建临时表进行验证。

5. 高级特性

我们将详细讲解SQL中的高级特性，包括公用表表达式（CTE）、子查询和窗口函数。我们将使用 WITH 子句创建临时表 employees 和 departments ，然后进行各种高级查询。

5.1 使用 WITH 子句创建临时表

首先，我们创建两个临时表 employees 和 departments 。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000




    
 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')

5.2 公用表表达式（CTE）

公用表表达式（CTE）是一个临时结果集，可以在查询中多次引用。CTE 使用 WITH 子句定义。

示例

要计算每个部门的平均薪水，并使用 CTE 来简化查询： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部'),department_avg_salary AS (--每个部门平均薪水    SELECT department_id, AVG(salary) AS avg_salary    FROM employees    GROUP BY department_id)SELECT d.department_name, das.avg_salaryFROM departments dJOIN department_avg_salary das ON d.id = das.department_id;

结果

department_name

avg_salary

销售部

4750.0

市场部

5833.3

技术部

7500.0

5.3 子查询

子查询是嵌套在另一个查询中的查询。子查询可以出现在 SELECT 、 FROM 、 WHERE 子句中。

示例

要获取薪水高于平均薪水的员工： 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salaryFROM employeesWHERE salary > (SELECT AVG(salary) FROM employees);

结果：

name

salary

李四

6000

赵六

7000

孙七

6500

周八

7500

钱十一

8000

5.4 常用的窗口函数

窗口函数对一组行进行计算，并返回每个行的计算结果。窗口函数通常与 OVER 子句一起使用。

序号

函数

含义

语法

1

ROW_NUMBER( )

为每一行分配一个唯一的序号。

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

2

RANK()

为每一行分配一个排名，相同值会分配相同的排名，但会 跳过 后续的排名。

RANK() OVER (PARTITION BY ... ORDER BY ...)

3

DENSE_RANK()

为每一行分配一个排名，相同值会分配相同的排名，但 不会跳过 后续的排名

DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)

4

NTILE(n)

将结果集分成 n 个桶，并为每一行分配一个桶号

NTILE(n) OVER (PARTITION BY ... ORDER BY ...)

5

LAG()

LEAD()

访问前一行或后一行的数据。

LAG(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

LEAD(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

6

FIRST_VALUE() LAST_VALUE()

返回窗口中的第一个值和最后一个值。

FIRST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)

LAST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)

7

聚合函数

聚合函数也可以作为窗口函数使用，如 SUM() , AVG() , MAX() , COUNT() ...

SUM(column) OVER (PARTITION BY ... ORDER BY ...)

ROW_NUMBER()，RANK() 和 DENSE_RANK() 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION




    
 ALL    SELECT 10, '王十二', 1, 4000)SELECT    name, department_id, salary,   ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_number,   RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_salary,   DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_salaryFROM employees

结果：

name

department_id

salary

ROW_NUMBER

rank_salary

dense_rank_salary

张三

1

5000

1

1

1

王五

1

4500

2

2

2

吴九

1

4500

3

2

2

王十二

1

4000

4

4

3

孙七

2

6500

1

1

1

李四

2

6000

2

2

2

郑十

2

5000

3

3

3

钱十一

3

8000

1

1

1

周八

3

7500

2

2

2

赵六

3

7000

3

3

3

NTILE(2) 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    NTILE(2) OVER (PARTITION BY department_id ORDER BY salary DESC) AS ntile_2FROM     employees;

结果：

name
department_id
salary
ntile
张三
1
5000
1
王五
1
4500
1
吴九
1
4500
2
王十二
1
4000
2
钱十一
3
8000
1
周八
3
7500
1
赵六
3
7000
2
孙七
2
6500
1
李四
2
6000
1
郑十
2
5000
2


LAG() 和 LEAD() 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary) AS lag_salary,    LEAD(salary, 1, 0) OVER (PARTITION BY department_id 




    
ORDER BY salary) AS lead_salaryFROM employees;

结果：

name
department_id
salary
lag
lead
王十二
1
4000
0
4500
王五
1
4500
4000
4500
吴九
1
4500
4500
5000
张三
1
5000
4500
0
赵六
3
7000
0
7500
周八
3
7500
7000
8000
钱十一
3
8000
7500
0
郑十
2
5000
0
6000
李四
2
6000
5000
6500
孙七
2
6500
6000
0


FIRST_VALUE() 和 LAST_VALUE() 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 




    
7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_value_salary,    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS last_value_salaryFROM employees;

结果

name
department_id
salary
first_value_salary
last_value_salary
郑十
2
5000
5000
5000
李四
2
6000
5000
6000
孙七
2
6500
5000
6500
王十二
1
4000
4000
4000
王五
1
4500
4000
4500
吴九
1
4500
4000
4500
张三
1
5000
4000
5000
赵六
3
7000
7000
7000
周八
3
7500
7000
7500
钱十一
3
8000
7000
8000


💡注意： LAST_VALUE() 默认情况下会在窗口内逐行计算，如果需要在整个分区计算，可以使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_value_salary,    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value_salaryFROM employees;

结果：

name
department_id
salary
first_value_salary
last_value_salary
郑十
2
5000
5000
6500
李四
2
6000
5000
6500
孙七
2
6500
5000
6500
赵六
3
7000
7000
8000
周八
3
7500
7000
8000
钱十一
3
8000
7000
8000
王十二
1
4000
4000
5000
王五
1
4500
4000
5000
吴九
1
4500
4000
5000
张三
1
5000
4000
5000


常用聚合函数在窗口中的用法

聚合函数在窗口中的用法允许你在窗口内进行聚合计算，例如计算窗口内的总和、平均值等。这通常使用 OVER 子句来定义窗口。

序号

函数

含义

1

SUM()

  • 计算窗口内行的累积和。

2

AVG()

  • 计算窗口内行的平均值。

3

COUNT()

  • 计算窗口内行的数量。

4

MAX() 和 MIN()

  • 计算窗口内行的最大值和最小值。

1. SUM() - 累积和

计算每个员工的累积薪水（按薪水降序排列）。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL




    
    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, salary,    SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salaryFROM     employees;

结果：

name
salary
cumulative_salary
钱十一
8000
8000
周八
7500
15500
赵六
7000
22500
孙七
6500
29000
李四
6000
35000
王五
5500
40500
张三
5000
45500
郑十
5000
50500
吴九
4500
55000
王十二
4000
59000


💡在这个示例中， SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 计算从第一个行到当前行的累积薪水。

2. AVG() - 移动平均

计算每个员工的移动平均薪水（按薪水降序排列，窗口大小为3）。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 




    
UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salary,       AVG(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_salaryFROM employees;

结果：

name
salary
moving_avg_salary
钱十一
8000
8,000.00
周八
7500
7,750.00
赵六
7000
7,500.00
孙七
6500
7,000.00
李四
6000
6,500.00
王五
5500
6,000.00
张三
5000
5,500.00
郑十
5000
5,166.67
吴九
4500
4,833.33
王十二
4000
4,500.00


💡在这个示例中， AVG(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 计算当前行及其前两行的平均薪水。

3. COUNT() - 窗口内行数

计算每个员工的窗口内行数（按薪水降序排列，窗口大小为3）。 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary 




    
UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salary,       COUNT(*) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_countFROM employees;

结果：

name
salary
window_count
钱十一
8000
1
周八
7500
2
赵六
7000
3
孙七
6500
3
李四
6000
3
王五
5500
3
郑十
5000
3
张三
5000
3
吴九
4500
3
王十二
4000
3


💡在这个示例中， COUNT(*) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 计算当前行及其前两行的行数。

4. MAX() 和 MIN() - 窗口内最大值和最小值

计算每个员工的窗口内最大值和最小值（按薪水降序排列，窗口大小为3）。 

WITH employees AS (    SELECT 1




    
 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salary,       MAX(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS max_salary,       MIN(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS min_salaryFROM employees;

结果：

name
salary
max_salary
min_salary
钱十一
8000
8000
8000
周八
7500
8000
7500
赵六
7000
8000
7000
孙七
6500
7500
6500
李四
6000
7000
6000
王五
5500
6500
5500
张三
5000
6000
5000
郑十
5000
5500
5000
吴九
4500
5000
4500
王十二
4000
5000
4000





💡在这个示例中， MAX(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 和 MIN(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 分别计算当前行及其前两行的最大值和最小值。

5.5 总结

通过上述示例，你可以看到如何使用 FIRST_VALUE() 和 LAST_VALUE() 窗口函数来获取窗口内的第一个和最后一个值，以及如何在窗口中使用聚合函数（如 SUM() 、 AVG() 、 COUNT() 、 MAX() 和 MIN() ）进行复杂的计算。

如果大家对SQL比较熟悉，学得比较好，想了解具体的代码的掌握情况，可以在 CDA认证 小程序上刷模拟题测试效果。

图片


6. 案例

下是三个综合的SQL案例，涵盖了我们之前学习的各种概念，包括数据检索、聚合与分组、多表操作以及高级特性（如窗口函数和公用表表达式）。这些案例将帮助你更好地理解和应用这些知识。

6.1 案例：员工薪资分析

6.1.1 目标

分析每个部门的员工薪资情况，包括平均薪资、最高薪资、最低薪资以及每个员工的薪资排名。

6.1.2 数据准备 

WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    






请到「今天看啥」查看全文


推荐文章
艺恩数据  ·  【艺恩报告】2025家电“国补”焕新社媒研究
昨天
CDA数据分析师  ·  Deepseek来袭，数据分析师会失业吗？
2 天前
数据派THU  ·  人工智能是不是走错了方向？
5 天前
数据派THU  ·  十宗「最」！2024 年 AI 大事件回顾，揭示隐藏风口与行业挑战
2 天前
数据派THU  ·  【博士论文】学习视觉-语言表示以实现多模态理解
6 天前
广东公共DV现场  ·  连男人也不放过！深圳一男厕所现变态偷拍……
8 年前
中央广电总台中国之声  ·  新规要来了，戒网暴力该收手了！
8 年前
乐趣微生活  ·  生理课上，老师让我实践...【NO6】
7 年前
佳木斯日报  ·  馆长任性，壕掷大礼！兵蚁射箭馆，年中体验月！
7 年前
晚安少年  ·  喜欢乖的又拒绝不了骚的？？？
7 年前
Sov5搜索   ·   小百科   ·   今天看啥   ·   移动版
51好读 - 好文章就要读起来!