未记录事实核验 · 状态：维护中

记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。

warning 适用边界与维护说明

本文适用于已有聚合、连接及窗口函数基础的读者练习多级子查询、业务分析和查询重写，不是三种数据库可原样执行的统一脚本：日期表达式主要采用MySQL方言，SQLite不支持文中的ANY/ALL写法，窗口函数和CTE也需确认数据库版本支持。业务示例应按代码口径理解：产品销售排行将近12个月销售额与未限定日期的各类别平均销售额之均值比较，排名仅覆盖通过筛选的产品；流失预警阈值是至少三笔已完成订单用户的平均订单间隔再取均值，并非每位用户自己的历史间隔。重写章节的“❌ 原始子查询版本”只是标记不当，该IN查询本身并非错误，JOIN、EXISTS和CTE也不保证更快，应保持结果语义并以执行计划及测试比较。本次仅作全文结构审阅，未进行数据库实测或外部事实核验。

🪆 SQL子查询（Subquery）

abstract 📋 本节概览

  • 🎯 学习目标：掌握子查询的各种类型和应用场景，实现复杂的数据检索和分析
  • 📚 前置知识：WHERE条件筛选
  • ⏱️ 预计用时：30分钟
  • 🔧 开发环境：MySQL/PostgreSQL/SQLite
  • 📊 数据集：users、orders等关系型表结构

🔧 技术定义

子查询（Subquery），也称为内嵌查询或嵌套查询，是指在一个SQL语句内部包含另一个完整的SELECT语句。子查询为主查询提供数据或条件，实现复杂的数据检索逻辑。子查询可以出现在SELECT、FROM、WHERE、HAVING等子句中，支持单层或多层嵌套。

🛠️ 执行机制

处理方式：

  1. 独立子查询：先执行子查询，将结果传递给主查询
  2. 相关子查询：逻辑上按外层查询行的关联值求值，实际执行方式可能被数据库优化器改写
  3. 结果传递：子查询结果作为主查询的条件或数据源
  4. 优化处理：数据库引擎可能将子查询重写为连接查询以提高性能

核心特征：

  • 嵌套结构：查询内部包含其他查询语句
  • 逻辑分层：支持复杂的分步查询逻辑
  • 结果依赖：内层查询为外层查询提供数据或条件
  • 灵活性强：可以实现复杂的业务查询需求

📝 子查询基本类型

🎯 标量子查询

标量子查询返回单一值，通常与比较运算符配合使用：

-- 查找薪资高于平均薪资的员工
SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查找最新订单的详细信息
SELECT order_id, user_id, order_date, order_amount
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

-- 查找特定用户的订单数量
SELECT user_id, username,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.user_id) as order_count
FROM users u
WHERE status = 'active';

-- 比较子查询：查找高于部门平均薪资的员工
SELECT e1.employee_id, e1.name, e1.department, e1.salary
FROM employees e1
WHERE salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

📋 列表子查询

列表子查询返回多个值，通常与IN、NOT IN、ANY、ALL等操作符配合：

-- 使用IN：查找有订单的用户
SELECT user_id, username, email
FROM users
WHERE user_id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE order_status = 'completed'
);

-- 使用NOT IN：查找没有订单的用户
SELECT user_id, username, email
FROM users
WHERE user_id NOT IN (
    SELECT user_id
    FROM orders
    WHERE user_id IS NOT NULL
);

-- 使用ANY：查找薪资高于任一销售员工的员工
SELECT employee_id, name, salary, department
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
);

-- 使用ALL：查找薪资高于所有销售员工的员工
SELECT employee_id, name, salary, department
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
);

📊 表子查询

表子查询返回完整的结果集，可以作为数据源在FROM子句中使用：

-- 派生表：从统计结果中进一步查询
SELECT dept_stats.department, dept_stats.avg_salary
FROM (
    SELECT 
        department,
        AVG(salary) as avg_salary,
        COUNT(*) as employee_count
    FROM employees
    GROUP BY department
) as dept_stats
WHERE dept_stats.employee_count > 5
ORDER BY dept_stats.avg_salary DESC;

-- 复杂的派生表查询
SELECT user_summary.user_type, COUNT(*) as type_count
FROM (
    SELECT 
        user_id,
        username,
        CASE 
            WHEN total_orders >= 10 THEN 'Frequent'
            WHEN total_orders >= 3 THEN 'Regular'
            ELSE 'Occasional'
        END as user_type
    FROM (
        SELECT 
            u.user_id,
            u.username,
            COUNT(o.order_id) as total_orders
        FROM users u
        LEFT JOIN orders o ON u.user_id = o.user_id
        GROUP BY u.user_id, u.username
    ) order_stats
) user_summary
GROUP BY user_summary.user_type;

🔍 高级子查询技术

🔗 相关子查询

相关子查询引用外层查询的列，逻辑上按外层行的关联值求值；实际是否逐行执行取决于数据库的执行计划：

-- 查找每个部门薪资最高的员工
SELECT e1.employee_id, e1.name, e1.department, e1.salary
FROM employees e1
WHERE salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- 查找用户的最新订单
SELECT u.user_id, u.username, o.order_date, o.order_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.user_id = u.user_id
);

-- 查找购买了特定类别产品的用户
SELECT u.user_id, u.username, u.email
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.product_id
    WHERE o.user_id = u.user_id
      AND p.category = 'electronics'
);

🎭 EXISTS和NOT EXISTS

EXISTS运算符检查子查询是否返回任何结果，适合表达存在性条件；与IN的性能差异取决于数据库优化器、索引和数据分布：

-- 查找有订单的活跃用户
SELECT u.user_id, u.username, u.registration_date
FROM users u
WHERE u.status = 'active'
  AND EXISTS (
      SELECT 1
      FROM orders o
      WHERE o.user_id = u.user_id
        AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  );

-- 查找没有任何订单的用户
SELECT u.user_id, u.username, u.registration_date
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
);

-- 查找购买了所有必需产品的用户
SELECT u.user_id, u.username
FROM users u
WHERE NOT EXISTS (
    SELECT p.product_id
    FROM products p
    WHERE p.category = 'essential'
      AND p.product_id NOT IN (
          SELECT oi.product_id
          FROM orders o
          INNER JOIN order_items oi ON o.order_id = oi.order_id
          WHERE o.user_id = u.user_id
            AND o.order_status = 'completed'
      )
);

🔄 多级嵌套子查询

复杂业务场景可能需要多层嵌套的子查询：

-- 多级嵌套：查找购买了达到指定订单明细行数阈值的产品的用户
SELECT u.user_id, u.username, u.email
FROM users u
WHERE u.user_id IN (
    SELECT DISTINCT o.user_id
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE oi.product_id IN (
        SELECT p.product_id
        FROM products p
        WHERE p.product_id IN (
            SELECT oi2.product_id
            FROM order_items oi2
            GROUP BY oi2.product_id
            HAVING COUNT(*) >= (
                SELECT COUNT(*) * 0.1  -- 阈值为不同产品数的10%，并非热度排名前10%
                FROM (SELECT DISTINCT product_id FROM order_items) as distinct_products
            )
        )
    )
);

-- 复杂业务逻辑：客户生命周期价值分析
SELECT 
    user_id,
    username,
    first_order_date,
    latest_order_date,
    total_spent,
    order_count,
    avg_order_value
FROM (
    SELECT 
        u.user_id,
        u.username,
        (SELECT MIN(o1.order_date) FROM orders o1 WHERE o1.user_id = u.user_id) as first_order_date,
        (SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.user_id = u.user_id) as latest_order_date,
        (SELECT COALESCE(SUM(o3.order_amount), 0) FROM orders o3 WHERE o3.user_id = u.user_id AND o3.order_status = 'completed') as total_spent,
        (SELECT COUNT(*) FROM orders o4 WHERE o4.user_id = u.user_id) as order_count,
        (SELECT COALESCE(AVG(o5.order_amount), 0) FROM orders o5 WHERE o5.user_id = u.user_id AND o5.order_status = 'completed') as avg_order_value
    FROM users u
    WHERE u.status = 'active'
) user_stats
WHERE total_spent > 1000
ORDER BY total_spent DESC;

🏢 业务应用场景

📊 数据分析与报表

-- 客户细分分析
SELECT 
    customer_segment,
    COUNT(*) as customer_count,
    AVG(total_spent) as avg_spent_per_segment
FROM (
    SELECT 
        u.user_id,
        u.username,
        CASE 
            WHEN total_orders >= 20 AND total_spent >= 10000 THEN 'VIP'
            WHEN total_orders >= 10 AND total_spent >= 5000 THEN 'Premium'
            WHEN total_orders >= 3 AND total_spent >= 1000 THEN 'Regular'
            ELSE 'Basic'
        END as customer_segment,
        total_spent
    FROM users u
    INNER JOIN (
        SELECT 
            user_id,
            COUNT(*) as total_orders,
            SUM(order_amount) as total_spent
        FROM orders
        WHERE order_status = 'completed'
        GROUP BY user_id
    ) order_stats ON u.user_id = order_stats.user_id
) segmented_customers
GROUP BY customer_segment
ORDER BY avg_spent_per_segment DESC;

-- 产品销售排行分析
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    sales_stats.total_sold,
    sales_stats.total_revenue,
    RANK() OVER (PARTITION BY p.category ORDER BY sales_stats.total_revenue DESC) as category_rank
FROM products p
INNER JOIN (
    SELECT 
        oi.product_id,
        SUM(oi.quantity) as total_sold,
        SUM(oi.quantity * oi.unit_price) as total_revenue
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
      AND o.order_status = 'completed'
    GROUP BY oi.product_id
) sales_stats ON p.product_id = sales_stats.product_id
WHERE sales_stats.total_revenue > (
    SELECT AVG(category_avg.avg_revenue)
    FROM (
        SELECT 
            p2.category,
            AVG(ss2.total_revenue) as avg_revenue
        FROM products p2
        INNER JOIN (
            SELECT 
                oi2.product_id,
                SUM(oi2.quantity * oi2.unit_price) as total_revenue
            FROM order_items oi2
            INNER JOIN orders o2 ON oi2.order_id = o2.order_id
            WHERE o2.order_status = 'completed'
            GROUP BY oi2.product_id
        ) ss2 ON p2.product_id = ss2.product_id
        GROUP BY p2.category
    ) category_avg
)
ORDER BY p.category, category_rank;

🎯 营销和用户运营

-- 用户流失预警分析
SELECT 
    u.user_id,
    u.username,
    u.email,
    last_activity.last_order_date,
    last_activity.days_since_last_order,
    user_profile.total_spent,
    user_profile.avg_order_frequency
FROM users u
INNER JOIN (
    SELECT 
        user_id,
        MAX(order_date) as last_order_date,
        DATEDIFF(CURDATE(), MAX(order_date)) as days_since_last_order
    FROM orders
    WHERE order_status = 'completed'
    GROUP BY user_id
) last_activity ON u.user_id = last_activity.user_id
INNER JOIN (
    SELECT 
        user_id,
        SUM(order_amount) as total_spent,
        COUNT(*) / NULLIF(DATEDIFF(CURDATE(), MIN(order_date)), 0) * 30 as avg_order_frequency
    FROM orders
    WHERE order_status = 'completed'
    GROUP BY user_id
) user_profile ON u.user_id = user_profile.user_id
WHERE last_activity.days_since_last_order > (
    SELECT AVG(days_between_orders)
    FROM (
        SELECT
            user_id,
            AVG(DATEDIFF(order_date, previous_order_date)) as days_between_orders
        FROM (
            SELECT
                user_id,
                order_date,
                LAG(order_date) OVER (
                    PARTITION BY user_id ORDER BY order_date, order_id
                ) as previous_order_date
            FROM orders
            WHERE order_status = 'completed'
        ) order_intervals
        GROUP BY user_id
        HAVING COUNT(*) >= 3
    ) frequency_stats
) * 2  -- 超过平均订单间隔2倍的用户
ORDER BY user_profile.total_spent DESC;

-- 个性化推荐候选用户
SELECT DISTINCT u.user_id, u.username, u.email
FROM users u
WHERE u.user_id IN (
    -- 购买了相似产品的用户
    SELECT DISTINCT o1.user_id
    FROM orders o1
    INNER JOIN order_items oi1 ON o1.order_id = oi1.order_id
    WHERE oi1.product_id IN (
        SELECT p.product_id
        FROM products p
        WHERE p.category IN (
            SELECT DISTINCT p2.category
            FROM order_items oi2
            INNER JOIN products p2 ON oi2.product_id = p2.product_id
            INNER JOIN orders o2 ON oi2.order_id = o2.order_id
            WHERE o2.user_id = 1001  -- 目标用户ID
        )
        AND p.product_id NOT IN (
            SELECT oi3.product_id
            FROM order_items oi3
            INNER JOIN orders o3 ON oi3.order_id = o3.order_id
            WHERE o3.user_id = 1001
        )
    )
    AND o1.user_id != 1001
);

⚡ 性能优化要点

🚀 子查询性能优化

  1. 比较EXISTS与IN：根据存在性语义选择写法，并通过执行计划与测试判断性能，不能仅凭数据集大小认定EXISTS更快
  2. 评估相关子查询重写：可尝试JOIN或预聚合等候选写法，保持结果语义一致，并通过执行计划与测试判断是否更合适
  3. 限制子查询结果：仅在业务语义允许截断结果时使用LIMIT；需要选取特定记录时应明确排序，并确认数据库支持该用法
  4. 索引优化：为子查询涉及的列创建适当索引
-- 待测试写法：使用IN和相关子查询
SELECT u.user_id, u.username
FROM users u
WHERE u.user_id IN (
    SELECT o.user_id
    FROM orders o
    WHERE o.order_date >= '2024-01-01'
      AND o.order_amount > (
          SELECT AVG(o2.order_amount)
          FROM orders o2
          WHERE o2.user_id = o.user_id
      )
);

-- 候选重写：使用JOIN和预聚合，是否更快需测试
SELECT DISTINCT u.user_id, u.username
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN (
    SELECT user_id, AVG(order_amount) as avg_amount
    FROM orders
    GROUP BY user_id
) user_avg ON o.user_id = user_avg.user_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_amount > user_avg.avg_amount;

-- 索引优化
CREATE INDEX idx_orders_user_date_amount ON orders(user_id, order_date, order_amount);
CREATE INDEX idx_users_status ON users(user_id, status);

📊 子查询重写优化

-- ❌ 原始子查询版本
SELECT p.product_id, p.product_name, p.price
FROM products p
WHERE p.product_id IN (
    SELECT oi.product_id
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_status = 'completed'
);

-- 重写为JOIN版本，性能需结合执行计划评估
SELECT DISTINCT p.product_id, p.product_name, p.price
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_status = 'completed';

-- 使用CTE组织查询以提高可读性，性能取决于数据库的执行计划
WITH recent_orders AS (
    SELECT DISTINCT oi.product_id
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= '2024-01-01'
      AND o.order_status = 'completed'
)
SELECT p.product_id, p.product_name, p.price
FROM products p
INNER JOIN recent_orders ro ON p.product_id = ro.product_id;

⚠️ 常见错误与注意事项

🚫 易错点分析

  1. 子查询返回多行错误：标量子查询返回多个值
  2. NULL值处理：NOT IN中包含NULL值的问题
  3. 性能问题：过度使用相关子查询
  4. 逻辑错误：子查询逻辑与预期不符
-- ❌ 错误：子查询返回多行
SELECT user_id, username
FROM users
WHERE user_id = (SELECT user_id FROM orders WHERE order_amount > 1000);

-- ✅ 正确：使用IN处理多行结果
SELECT user_id, username
FROM users
WHERE user_id IN (SELECT user_id FROM orders WHERE order_amount > 1000);

-- ❌ 错误：NOT IN遇到NULL值问题
SELECT user_id, username
FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders);  -- 如果orders中有NULL值会返回空结果

-- ✅ 正确：过滤NULL值
SELECT user_id, username
FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

-- 或使用NOT EXISTS
SELECT u.user_id, u.username
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

📋 最佳实践

✅ 设计原则

  1. 按语义选择查询结构：根据存在性判断、聚合和明细关联等需求选择子查询或JOIN；重写时确认结果语义一致，不默认JOIN性能更优
  2. 合理使用EXISTS：存在性检查优先使用EXISTS
  3. 避免深度嵌套：超过3层嵌套考虑重构
  4. 性能测试：复杂子查询需要性能测试验证
-- 标准的子查询应用模板
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        SUM(order_amount) as total_spent,
        AVG(order_amount) as avg_order_value,
        MAX(order_date) as last_order_date
    FROM orders
    WHERE order_status = 'completed'
    GROUP BY user_id
),
user_segments AS (
    SELECT 
        user_id,
        order_count,
        total_spent,
        CASE 
            WHEN total_spent >= 10000 THEN 'Premium'
            WHEN total_spent >= 5000 THEN 'Gold'
            WHEN total_spent >= 1000 THEN 'Silver'
            ELSE 'Bronze'
        END as segment
    FROM user_stats
)
SELECT 
    u.user_id,
    u.username,
    u.email,
    us.segment,
    us.order_count,
    us.total_spent
FROM users u
INNER JOIN user_segments us ON u.user_id = us.user_id
WHERE u.status = 'active'
  AND EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.user_id = u.user_id 
        AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  )
ORDER BY us.total_spent DESC;

✅ 技能检查清单

基础技能认证：

  • [ ] 理解子查询的基本概念和执行机制
  • [ ] 掌握标量子查询、列表子查询、表子查询的使用
  • [ ] 熟练使用IN、NOT IN、EXISTS、NOT EXISTS
  • [ ] 了解相关子查询和独立子查询的区别
  • [ ] 掌握子查询在不同子句中的应用
  • [ ] 理解子查询的常见错误和注意事项

进阶技能：

  • [ ] 能够编写复杂的多级嵌套子查询
  • [ ] 掌握子查询的性能优化技术
  • [ ] 熟练将子查询重写为JOIN操作
  • [ ] 理解CTE与子查询的关系和选择
  • [ ] 能够解决复杂业务场景的子查询需求
  • [ ] 掌握子查询在数据分析中的高级应用

下一步学习：Hive/窗口函数与 - 学习高级分析函数和数据处理