子查询 #

子查询类型
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   标量子查询 (Scalar Subquery)                              │
│   └── 返回单个值                                           │
│                                                             │
│   列子查询 (Column Subquery)                                │
│   └── 返回单列多行                                         │
│                                                             │
│   行子查询 (Row Subquery)                                   │
│   └── 返回单行多列                                         │
│                                                             │
│   表子查询 (Table Subquery)                                 │
│   └── 返回多行多列                                         │
│                                                             │
│   相关子查询 (Correlated Subquery)                          │
│   └── 引用外部查询                                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- SELECT 子句
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id)
FROM users;

-- WHERE 子句
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- FROM 子句
SELECT * FROM (SELECT * FROM users WHERE age > 25) AS u;

-- HAVING 子句
SELECT status, COUNT(*)
FROM users
GROUP BY status
HAVING COUNT(*) > (SELECT AVG(cnt) FROM (
    SELECT COUNT(*) AS cnt FROM users GROUP BY status
) t);

-- 返回单个值的子查询
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- 在 WHERE 中使用
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- 在表达式中使用
SELECT 
    name,
    age,
    age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- 标量子查询必须只返回一个值
-- 错误示例:
SELECT * FROM users
WHERE id = (SELECT user_id FROM orders);  -- 如果返回多行会报错

-- 正确做法:
SELECT * FROM users
WHERE id = (SELECT user_id FROM orders LIMIT 1);

-- 或使用聚合函数确保返回单个值
SELECT * FROM users
WHERE id = (SELECT MAX(user_id) FROM orders);

-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- NOT IN 子查询
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- 注意 NULL 值
SELECT * FROM users
WHERE id NOT IN (
    SELECT user_id FROM orders WHERE user_id IS NOT NULL
);

-- ANY: 任一满足条件
SELECT * FROM users
WHERE age > ANY (SELECT age FROM users WHERE status = 'active');
-- 等价于 age > MIN(age of active users)

-- ALL: 全部满足条件
SELECT * FROM users
WHERE age > ALL (SELECT age FROM users WHERE status = 'inactive');
-- 等价于 age > MAX(age of inactive users)

-- SOME 等同于 ANY
SELECT * FROM users
WHERE age > SOME (SELECT age FROM users WHERE status = 'active');

-- = ANY 等价于 IN
SELECT * FROM users
WHERE id = ANY (SELECT user_id FROM orders);

-- != ALL 等价于 NOT IN
SELECT * FROM users
WHERE id != ALL (SELECT user_id FROM orders);

-- > ANY
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'premium');

-- EXISTS: 存在满足条件的行
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- NOT EXISTS: 不存在满足条件的行
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- EXISTS 通常性能更好
-- EXISTS: 找到第一个匹配就停止
-- IN: 可能需要计算所有值

-- 使用 EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.amount > 100
);

-- 使用 IN
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 100
);

-- 建议:
-- - 子查询结果集大: 使用 EXISTS
-- - 主查询结果集大: 使用 IN

-- 检查用户是否有多个订单
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o1
    WHERE o1.user_id = u.id
    AND EXISTS (
        SELECT 1 FROM orders o2
        WHERE o2.user_id = u.id
        AND o2.id != o1.id
    )
);

-- 检查用户是否有特定产品的订单
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.user_id = u.id
    AND oi.product_name = 'Product A'
);

相关子查询
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   特点:                                                     │
│   ├── 子查询引用外部查询的列                               │
│   ├── 对外部每一行执行一次子查询                           │
│   └── 性能开销较大                                         │
│                                                             │
│   执行过程:                                                 │
│   1. 外部查询获取一行                                      │
│   2. 将值传递给子查询                                      │
│   3. 执行子查询                                            │
│   4. 返回结果给外部查询                                    │
│   5. 重复以上步骤                                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 查找每个用户最新的订单
SELECT u.name, o.order_no, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at = (
    SELECT MAX(created_at)
    FROM orders
    WHERE user_id = u.id
);

-- 查找高于平均年龄的用户
SELECT * FROM users u1
WHERE age > (
    SELECT AVG(age)
    FROM users u2
    WHERE u2.status = u1.status
);

-- 相关子查询可能很慢
-- 优化方法: 使用 JOIN 或窗口函数

-- 原始相关子查询
SELECT u.name, o.order_no, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at = (
    SELECT MAX(created_at)
    FROM orders
    WHERE user_id = u.id
);

-- 优化: 使用窗口函数
WITH ranked_orders AS (
    SELECT 
        o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
)
SELECT u.name, ro.order_no, ro.created_at
FROM users u
JOIN ranked_orders ro ON u.id = ro.user_id
WHERE ro.rn = 1;

-- 派生表 (FROM中的子查询)
SELECT u.name, o.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

-- 必须使用别名
SELECT * FROM (
    SELECT name, age FROM users
) AS u;  -- 必须有别名 AS u

-- 多层派生表
SELECT 
    summary.name,
    summary.total_orders,
    summary.total_amount
FROM (
    SELECT 
        u.name,
        COUNT(o.id) AS total_orders,
        COALESCE(SUM(o.amount), 0) AS total_amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name
) summary
WHERE summary.total_orders > 0
ORDER BY summary.total_amount DESC;

-- CTE (Common Table Expression)
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users;

-- 多个CTE
WITH 
active_users AS (
    SELECT * FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT 
    au.name,
    COALESCE(uo.order_count, 0) AS orders
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

-- 递归CTE: 组织结构
WITH RECURSIVE org_hierarchy AS (
    -- 基础查询: 顶级员工
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询: 下级员工
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM org_hierarchy ORDER BY level;

-- 递归CTE: 路径查找
WITH RECURSIVE paths AS (
    SELECT 
        id,
        name,
        name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.id,
        c.name,
        p.path || ' > ' || c.name
    FROM categories c
    JOIN paths p ON c.parent_id = p.id
)
SELECT * FROM paths;

CTE vs 派生表
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   CTE 优点:                                                 │
│   ├── 可读性好                                             │
│   ├── 可重用                                               │
│   ├── 支持递归                                             │
│   └── 逻辑清晰                                             │
│                                                             │
│   派生表优点:                                               │
│   ├── 内联更紧凑                                           │
│   └── 适合简单查询                                         │
│                                                             │
│   建议:                                                     │
│   ├── 复杂查询使用 CTE                                     │
│   ├── 简单查询使用派生表                                   │
│   └── 需要递归必须使用 CTE                                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

子查询性能优化
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 避免相关子查询                                         │
│   ├── 使用 JOIN 代替                                       │
│   └── 使用窗口函数代替                                     │
│                                                             │
│   2. 使用 EXISTS 代替 IN                                    │
│   ├── EXISTS 找到匹配就停止                                │
│   └── 性能更好                                             │
│                                                             │
│   3. 使用 CTE 提高可读性                                    │
│   ├── 逻辑清晰                                             │
│   └── 便于优化                                             │
│                                                             │
│   4. 确保子查询有索引                                       │
│   ├── 连接列创建索引                                       │
│   └── 加速子查询                                           │
│                                                             │
│   5. 限制子查询结果集                                       │
│   ├── 使用 LIMIT                                           │
│   └── 减少数据处理量                                       │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 查看子查询执行计划
EXPLAIN SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- 分析复杂子查询
EXPLAIN ANALYZE
WITH user_stats AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, us.order_count
FROM users u
LEFT JOIN user_stats us ON u.id = us.user_id;