子查询SUBQUERY - 元素码农

发布时间: 2025-04-02 22:56

↑

# 子查询SUBQUERY

本文将详细介绍SQLite中子查询的使用，包括基本语法、常见应用场景以及性能优化等内容。

## 基本概念

### 什么是子查询

子查询是嵌套在另一个SQL查询内部的查询。它可以出现在主查询的不同位置：

- SELECT子句中
- FROM子句中
- WHERE子句中
- HAVING子句中

## 子查询类型

### 标量子查询

1. **返回单个值**
   ```sql
   SELECT name,
          price,
          (SELECT AVG(price) FROM products) as avg_price
   FROM products
   WHERE price > (SELECT AVG(price) FROM products);
   ```

2. **在WHERE中使用**
   ```sql
   SELECT *
   FROM orders
   WHERE total_amount > (
       SELECT AVG(total_amount)
       FROM orders
       WHERE order_date >= date('now', '-30 days')
   );
   ```

### 列子查询

1. **IN操作符**
   ```sql
   SELECT *
   FROM products
   WHERE category_id IN (
       SELECT id
       FROM categories
       WHERE parent_id = 1
   );
   ```

2. **NOT IN操作符**
   ```sql
   SELECT *
   FROM customers
   WHERE id NOT IN (
       SELECT customer_id
       FROM orders
       WHERE order_date >= date('now', '-90 days')
   );
   ```

### 表子查询

1. **FROM子句中使用**
   ```sql
   SELECT category_name, avg_price
   FROM (
       SELECT 
           c.name as category_name,
           AVG(p.price) as avg_price
       FROM categories c
       JOIN products p ON c.id = p.category_id
       GROUP BY c.id
   ) as category_stats
   WHERE avg_price > 100;
   ```

2. **临时结果集**
   ```sql
   SELECT *
   FROM (
       SELECT 
           customer_id,
           COUNT(*) as order_count,
           SUM(total_amount) as total_spent
       FROM orders
       GROUP BY customer_id
   ) as customer_stats
   WHERE order_count > 5;
   ```

## 相关子查询

### EXISTS操作符

1. **基本用法**
   ```sql
   SELECT *
   FROM customers c
   WHERE EXISTS (
       SELECT 1
       FROM orders o
       WHERE o.customer_id = c.id
       AND o.total_amount > 1000
   );
   ```

2. **NOT EXISTS**
   ```sql
   SELECT *
   FROM products p
   WHERE NOT EXISTS (
       SELECT 1
       FROM order_items oi
       WHERE oi.product_id = p.id
   );
   ```

### 相关更新

```sql
UPDATE products
SET stock = stock - (
    SELECT SUM(quantity)
    FROM order_items
    WHERE order_items.product_id = products.id
    AND order_id IN (
        SELECT id FROM orders WHERE status = 'pending'
    )
);
```

## 高级应用

### 多层嵌套

```sql
SELECT *
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE id IN (
        SELECT order_id
        FROM order_items
        WHERE product_id IN (
            SELECT id
            FROM products
            WHERE category_id = 1
        )
    )
);
```

### 条件子查询

```sql
SELECT 
    product_name,
    price,
    CASE 
        WHEN price > (SELECT AVG(price) FROM products) THEN 'High'
        WHEN price < (SELECT AVG(price) FROM products) THEN 'Low'
        ELSE 'Average'
    END as price_category
FROM products;
```

## 性能优化

### 优化策略

1. **使用JOIN替代**
   ```sql
   -- 子查询版本
   SELECT *
   FROM orders
   WHERE customer_id IN (
       SELECT id FROM customers WHERE country = 'China'
   );
   
   -- JOIN版本（通常更快）
   SELECT o.*
   FROM orders o
   JOIN customers c ON o.customer_id = c.id
   WHERE c.country = 'China';
   ```

2. **EXISTS vs IN**
   ```sql
   -- 使用EXISTS（当子查询结果集较大时更高效）
   SELECT *
   FROM customers c
   WHERE EXISTS (
       SELECT 1
       FROM orders o
       WHERE o.customer_id = c.id
   );
   ```

### 索引使用

1. **子查询条件**
   ```sql
   -- 确保子查询中的连接列有索引
   CREATE INDEX idx_orders_customer
   ON orders(customer_id);
   ```

2. **关联条件**
   ```sql
   -- 相关子查询的连接条件应该使用索引
   CREATE INDEX idx_order_items_product
   ON order_items(product_id);
   ```

## 最佳实践

### 查询设计

1. **简化查询**
   - 避免不必要的嵌套
   - 考虑使用JOIN替代
   - 使用适当的子查询类型

2. **可读性**
   - 合理缩进
   - 使用有意义的别名
   - 添加注释说明

### 性能考虑

1. **查询优化**
   - 使用适当的索引
   - 避免复杂嵌套
   - 考虑查询重写

2. **资源管理**
   - 控制子查询层数
   - 限制结果集大小
   - 注意内存使用

## 总结

使用子查询需要注意：

- 选择合适的子查询类型
- 注意查询性能
- 保持代码可读性
- 合理使用索引

建议：

- 优先考虑简单查询
- 适当使用JOIN
- 注意性能优化
- 保持代码整洁