复合条件查询 - 元素码农

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

↑

# 复合条件查询

本文将详细介绍SQLite中复合条件查询的使用，包括多条件组合、条件优先级以及复杂条件的优化等内容。

## 基本条件组合

### AND和OR组合

1. **基本语法**
   ```sql
   SELECT * FROM table_name
   WHERE condition1 AND condition2
   OR condition3;
   ```

2. **示例**
   ```sql
   SELECT * FROM products
   WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);
   ```

### 条件优先级

1. **使用括号**
   ```sql
   SELECT * FROM orders
   WHERE (status = 'pending' OR status = 'processing')
   AND total_amount > 100;
   ```

2. **多层嵌套**
   ```sql
   SELECT * FROM employees
   WHERE (department = 'IT' AND (role = 'developer' OR role = 'designer'))
   OR (department = 'Sales' AND experience > 5);
   ```

## 高级条件组合

### IN和BETWEEN组合

1. **多值匹配**
   ```sql
   SELECT * FROM products
   WHERE category IN ('electronics', 'computers')
   AND price BETWEEN 100 AND 1000
   AND stock > 0;
   ```

2. **排除组合**
   ```sql
   SELECT * FROM orders
   WHERE status NOT IN ('cancelled', 'refunded')
   AND order_date BETWEEN date('now', '-30 days') AND date('now');
   ```

### LIKE模式组合

1. **多模式匹配**
   ```sql
   SELECT * FROM users
   WHERE (email LIKE '%.com'
   OR email LIKE '%.org')
   AND username LIKE 'admin%';
   ```

2. **复杂模式**
   ```sql
   SELECT * FROM products
   WHERE (name LIKE '%phone%' OR description LIKE '%phone%')
   AND (category = 'electronics' OR category = 'accessories');
   ```

## 子查询条件

### EXISTS组合

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

2. **多重EXISTS**
   ```sql
   SELECT * FROM products
   WHERE EXISTS (
       SELECT 1 FROM order_items
       WHERE order_items.product_id = products.id
   )
   AND EXISTS (
       SELECT 1 FROM inventory
       WHERE inventory.product_id = products.id
       AND stock > 10
   );
   ```

### IN子查询组合

```sql
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers
    WHERE country = 'China'
)
AND status IN (
    SELECT status FROM order_status
    WHERE is_active = 1
);
```

## 条件函数

### CASE表达式

1. **条件分类**
   ```sql
   SELECT *,
       CASE 
           WHEN price > 1000 AND category = 'electronics' THEN 'high-end'
           WHEN price > 500 OR (category = 'books' AND price > 100) THEN 'mid-range'
           ELSE 'budget'
       END as price_category
   FROM products;
   ```

2. **复杂逻辑**
   ```sql
   SELECT *,
       CASE
           WHEN stock = 0 THEN 'out of stock'
           WHEN stock < reorder_level AND EXISTS (
               SELECT 1 FROM order_items
               WHERE order_items.product_id = products.id
               AND created_at > date('now', '-7 days')
           ) THEN 'reorder soon'
           ELSE 'in stock'
       END as stock_status
   FROM products;
   ```

## 性能优化

### 索引使用

1. **复合索引**
   ```sql
   -- 为常用的复合条件创建索引
   CREATE INDEX idx_products_cat_price
   ON products(category, price);
   
   -- 使用索引的查询
   SELECT * FROM products
   WHERE category = 'electronics'
   AND price BETWEEN 100 AND 1000;
   ```

2. **索引选择**
   ```sql
   -- 优先使用选择性高的条件
   SELECT * FROM orders
   WHERE tracking_number = 'ABC123'  -- 高选择性
   AND status = 'pending';           -- 低选择性
   ```

### 查询优化

1. **简化条件**
   ```sql
   -- 优化前
   SELECT * FROM products
   WHERE category = 'electronics'
   AND (price >= 100 AND price <= 1000);
   
   -- 优化后
   SELECT * FROM products
   WHERE category = 'electronics'
   AND price BETWEEN 100 AND 1000;
   ```

2. **条件重写**
   ```sql
   -- 优化前
   SELECT * FROM orders
   WHERE status != 'completed'
   AND status != 'cancelled';
   
   -- 优化后
   SELECT * FROM orders
   WHERE status IN ('pending', 'processing');
   ```

## 最佳实践

### 条件设计

1. **清晰的结构**
   - 使用适当的缩进
   - 合理使用括号
   - 拆分复杂条件

2. **可维护性**
   - 使用有意义的别名
   - 添加注释说明
   - 保持一致的格式

### 性能考虑

1. **查询优化**
   - 使用合适的索引
   - 简化复杂条件
   - 优化执行计划

2. **资源管理**
   - 控制结果集大小
   - 避免全表扫描
   - 监控查询性能

## 总结

复合条件查询需要注意：

- 正确使用条件组合
- 注意条件优先级
- 合理使用索引
- 优化查询性能

建议：

- 保持条件清晰
- 适当使用子查询
- 注意性能优化
- 维护代码可读性