集合查询 - 元素码农

发布时间: 2025-04-02 23:07

↑

# 集合查询

本文将详细介绍SQLite中的集合查询操作，包括UNION、INTERSECT和EXCEPT等集合运算符的使用方法和最佳实践。

## 集合运算符概述

### 基本集合运算符

1. **UNION**: 合并两个结果集并去除重复行
2. **UNION ALL**: 合并两个结果集保留重复行
3. **INTERSECT**: 获取两个结果集的交集
4. **EXCEPT**: 获取第一个结果集中排除第二个结果集的部分

## UNION操作

### UNION基本用法

```sql
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
```

### UNION ALL示例

```sql
-- 获取所有活跃用户和新注册用户
SELECT user_id, username, 'active' as user_type
FROM active_users
UNION ALL
SELECT user_id, username, 'new' as user_type
FROM new_registrations;
```

### 注意事项

1. 列数必须相同
2. 对应列的数据类型必须兼容
3. 列名以第一个SELECT语句为准

## INTERSECT操作

### 基本语法

```sql
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;
```

### 实际应用

```sql
-- 查找同时是VIP会员且有优惠券的用户
SELECT user_id FROM vip_members
INTERSECT
SELECT user_id FROM coupon_holders;
```

## EXCEPT操作

### 基本语法

```sql
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;
```

### 使用场景

```sql
-- 查找有订单但未评价的用户
SELECT user_id FROM orders
EXCEPT
SELECT user_id FROM reviews;
```

## 复杂集合查询

### 多表集合

```sql
-- 获取所有类型的用户活动
SELECT user_id, 'purchase' as activity
FROM orders
UNION ALL
SELECT user_id, 'review' as activity
FROM reviews
UNION ALL
SELECT user_id, 'login' as activity
FROM login_logs;
```

### 条件过滤

```sql
-- 查找最近一个月活跃但未购买的用户
SELECT user_id FROM login_logs
WHERE login_time >= date('now', '-30 days')
EXCEPT
SELECT user_id FROM orders
WHERE order_date >= date('now', '-30 days');
```

## 性能优化

### 索引使用

1. **创建合适的索引**
   ```sql
   CREATE INDEX idx_user_id ON orders(user_id);
   CREATE INDEX idx_user_id ON reviews(user_id);
   ```

2. **避免全表扫描**
   ```sql
   -- 使用WHERE子句限制数据范围
   SELECT id FROM table1 WHERE date >= date('now', '-7 days')
UNION
SELECT id FROM table2 WHERE date >= date('now', '-7 days');
   ```

### 优化策略

1. **使用UNION ALL替代UNION**
   - 当确定没有重复数据时
   - 当允许重复数据时

2. **合理使用WHERE子句**
   - 在UNION之前过滤数据
   - 减少需要处理的数据量

## 最佳实践

### 代码规范

1. **清晰的格式化**
   ```sql
   SELECT column1, column2
   FROM table1
   WHERE condition1
   UNION
   SELECT column1, column2
   FROM table2
   WHERE condition2;
   ```

2. **使用注释**
   ```sql
   -- 获取所有活跃用户
   SELECT user_id
   FROM daily_active_users
   UNION
   -- 获取所有新注册用户
   SELECT user_id
   FROM new_users;
   ```

### 调试技巧

1. **使用子查询验证**
   ```sql
   SELECT * FROM (
       SELECT user_id FROM table1
       UNION
       SELECT user_id FROM table2
   ) AS combined_result
   WHERE user_id = 'test_user';
   ```

2. **结果集验证**
   ```sql
   -- 检查结果集大小
   SELECT COUNT(*) FROM (
       SELECT column1 FROM table1
       UNION
       SELECT column1 FROM table2
   );
   ```

## 总结

集合查询操作要点：

- 选择合适的集合运算符
- 确保列数和数据类型匹配
- 注意性能优化
- 保持代码可读性

建议：

- 优先使用UNION ALL
- 合理使用索引
- 注意代码格式化
- 添加必要的注释