sql子查询

深入理解

这个查询是一个典型的相关子查询（Correlated Subquery），它的执行方式与普通子查询不同。让我们一步步拆解它的执行逻辑：

SELECT name, salary
FROM employees e  -- 主查询表别名 e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees  -- 子查询使用原始表名（实际指向同一张表）
    WHERE department = e.department  -- 关键：关联条件
);

1. 主查询先执行
   数据库首先从employees表（别名为e）中取第一行数据，假设该行数据：

      { name: "张三", salary: 8000, department: "技术部" }
   

2. 传递参数到子查询
   将主查询当前行的department值（"技术部"）传递给子查询：

   -- 子查询变成：
   SELECT AVG(salary) 
   FROM employees 
   WHERE department = '技术部';  -- 注意这里变成了具体值

3. 执行子查询
   计算技术部的平均工资，假设得到结果：7500

4. 主查询条件判断
   比较主查询当前行的salary和子查询结果：

   8000 > 7500 → 条件成立

此行数据被保留

5. 循环处理
   移动到主查询的下一行数据，重复步骤2-4，直到处理完所有行

graph LR A[主查询取第一行数据] --> B[传递部门值到子查询] B --> C[执行部门过滤的子查询] C --> D[比较工资条件] D -->|满足| E[保留该行] D -->|不满足| F[丢弃] E --> G[取下一行] F --> G G --> H{还有数据?} H -->|是| B H -->|否| I[输出结果]

理解技巧：想象成循环嵌套

• 外层循环：主查询的每一行 (for each row in employees)
• 内层循环：每次针对当前行部门计算平均工资

⚠️ 性能警告

这种写法在数据量大时可能很慢，因为：

• 每个员工都要执行一次子查询
• 假设有1000名员工 → 执行1000次AVG计算

🚀 优化方案：使用窗口函数（推荐）

SELECT name, salary
FROM (
  SELECT 
    name,
    salary,
    department,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg  -- 直接计算部门均值
  FROM employees
) AS temp
WHERE salary > dept_avg;

两种方式对比

方法	执行次数	大数据量性能
相关子查询	N次（N=员工数）	差
窗口函数	1次全表扫描	优

即使不熟悉窗口函数，理解相关子查询的逐行执行特性对写出高效SQL至关重要！

理解这个相关子查询的执行逻辑是SQL进阶的关键！让我们用更直观的方式拆解这个查询：

🔍 执行步骤详解（带可视化模拟）

SELECT name, salary
FROM employees e  -- 主查询表（别名为e）
WHERE salary > (
    SELECT AVG(salary)
    FROM employees  -- 子查询表
    WHERE department = e.department  -- 关键连接点
);

🌀 执行流程（想象数据库实际运作）：

graph TB A[开始] --> B[主查询取第一行员工数据] B --> C[获取该员工部门：e.department] C --> D[用此部门值执行子查询] D --> E[计算该部门的平均工资] E --> F[比较：员工工资 > 部门平均？] F -->|是| G[保留该行] F -->|否| H[丢弃该行] G --> I[取下一行员工数据] H --> I I --> J{还有数据？} J -->|是| C J -->|否| K[返回最终结果]

📌 关键理解点

1. 不是先执行子查询！
   数据库是逐行处理主查询，对每一行：

   • 暂停主查询
   • 用当前行的部门值执行子查询
   • 恢复主查询

2. 子查询值动态变化
   示例数据：

   name	salary	department
   张三	8000	技术部
   李四	6000	技术部
   王五	9000	销售部

   处理过程：

   • 处理张三时：子查询计算技术部平均工资=(8000+6000)/2=7000
     • 8000 > 7000 → 保留
   • 处理李四时：子查询仍计算技术部平均工资=7000
     • 6000 > 7000？→ 不成立 → 丢弃
   • 处理王五时：子查询计算销售部平均工资=9000
     • 9000 > 9000？→ 不成立 → 丢弃

3. 本质是双层循环

   // 伪代码解释
   const result = [];
   
   for (const emp of employees) {         // 主查询循环
     const deptAvg = calculateAvg(        // 子查询计算
       "SELECT AVG(salary) FROM employees WHERE department = ?", 
       emp.department
     );
     
     if (emp.salary > deptAvg) {          // WHERE条件判断
       result.push(emp);
     }
   }
   return result;
   

⚠️ 性能警告（大数据集慎用！）

这种写法在10,000行数据时：

• 主查询：10,000次循环
• 子查询：10,000次执行（每次都要扫描整个部门）
• 总操作量 ≈ 10,000 × 表行数

🚀 优化方案：使用窗口函数

SELECT name, salary
FROM (
  SELECT *,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
  FROM employees
) AS tmp
WHERE salary > dept_avg;

优势：只扫描一次表，性能提升百倍！

💎 理解技巧

1. 把主查询的 e.department 看作传递给子查询的参数
2. 想象每个员工都要"打电话"问HR："我部门的平均工资是多少？"
3. 只有自己工资超过HR回复的数字，才能进入最终名单

掌握相关子查询的执行顺序，你就解锁了SQL最强大的功能之一！但记住：能用JOIN或窗口函数解决的问题，尽量不用相关子查询。