第18章：子查询与高级查询技巧

本章目标

学完本章后，你将能够：

1. 在 WHERE、FROM、SELECT 中编写子查询
2. 使用 Scan 映射查询结果到 map 或自定义结构体
3. 使用 Row() / Rows() 逐行处理大结果集
4. 使用命名参数让复杂 SQL 更清晰
5. 理解子查询的性能影响

前置知识

• 需要先学习：第11-17章（查询进阶）
• 需要了解：MySQL 子查询语法

---

18.1 WHERE 中的子查询

基础子查询

// 查询年龄大于平均年龄的用户
var users []User
subQuery := db.Model(&User{}).Select("AVG(age)")
db.Where("age > (?)", subQuery).Find(&users)
// SQL: SELECT * FROM `users` WHERE age > (SELECT AVG(age) FROM `users`)

IN 子查询

// 查询有过订单的用户
db.Where("id IN (?)", db.Model(&Order{}).Select("user_id")).Find(&users)
// SQL: SELECT * FROM `users` WHERE id IN (SELECT user_id FROM `orders`)

// 查询订单金额大于 100 的用户
db.Where("id IN (?)",
	db.Model(&Order{}).Select("user_id").Where("amount > ?", 100),
).Find(&users)
// SQL: SELECT * FROM `users`
//      WHERE id IN (SELECT user_id FROM `orders` WHERE amount > 100)

关联子查询

// 查询订单金额大于该用户平均订单金额的订单
db.Where("amount > (?)",
	db.Model(&Order{}).Select("AVG(amount)").
		Where("user_id = orders.user_id"), // 关联条件
).Find(&orders)
// SQL: SELECT * FROM `orders` o
//      WHERE amount > (SELECT AVG(amount) FROM `orders` WHERE user_id = o.user_id)

---

18.2 FROM 中的子查询（派生表）

// 从子查询结果中再查询
subQuery := db.Model(&Order{}).
	Select("user_id, SUM(amount) as total_amount").
	Group("user_id")

var results []struct {
	UserID      uint
	TotalAmount float64
}
db.Table("(?) as user_stats", subQuery).
	Where("total_amount > ?", 1000).
	Order("total_amount desc").
	Find(&results)

// SQL: SELECT * FROM (
//   SELECT user_id, SUM(amount) as total_amount FROM orders GROUP BY user_id
// ) as user_stats WHERE total_amount > 1000 ORDER BY total_amount desc

---

18.3 SELECT 中的子查询

// 在主查询中嵌入子查询作为一列
var results []struct {
	ID          uint
	Name        string
	OrderCount  int64
}
db.Model(&User{}).
	Select("id, name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count").
	Scan(&results)

// SQL: SELECT id, name,
//   (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count
// FROM users

---

18.4 Scan：灵活的结果映射

扫描到 Map

var result map[string]interface{}
db.Model(&User{}).Select("name, age").Where("id = ?", 1).Scan(&result)
// 但 GORM 不直接支持单行扫描到 map，更推荐自定义结构体

// 多行扫描
var results []map[string]interface{}
db.Model(&User{}).Select("name, age").Find(&results)
for _, row := range results {
	fmt.Println(row["name"], row["age"])
}

扫描到自定义结构体

// 定义只包含需要字段的结构体
type SimpleUser struct {
	Name string
	Age  int
}

var simpleUsers []SimpleUser
db.Model(&User{}).Select("name", "age").Find(&simpleUsers)

---

18.5 Row() / Rows()：逐行处理

当结果集非常大时，一次性加载到内存不合适：

// Rows()：逐行遍历，内存友好
rows, err := db.Model(&User{}).Where("age > ?", 18).Rows()
if err != nil {
	log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
	var user User
	// 注意：这里用 db.ScanRows，不是 rows.Scan
	db.ScanRows(rows, &user)
	
	// 处理这一行
	fmt.Println(user.Name)
	
	// 不会把全部数据加载到内存
}

📌 Rows() 返回的是 *sql.Rows，不会一次性加载所有数据。适合处理百万级数据。

---

18.6 命名参数

让长 SQL 更可读：

// 使用 sql.Named
db.Where("name = @name AND age > @minAge AND age < @maxAge",
	sql.Named("name", "张三"),
	sql.Named("minAge", 18),
	sql.Named("maxAge", 60),
).Find(&users)

// 使用 map（更简洁）
db.Where("name = @name AND age > @age",
	map[string]interface{}{
		"name": "张三",
		"age":  18,
	},
).Find(&users)

---

18.7 完整实战：订单报表查询

// 需求：每个用户的订单统计，包括用户名、订单数、总金额、最大订单金额
type UserOrderReport struct {
	UserName   string
	OrderCount int64
	TotalSpent float64
	MaxAmount  float64
	AvgAmount  float64
}

var reports []UserOrderReport

subQuery := db.Model(&Order{}).
	Select("user_id, COUNT(*) as cnt, SUM(amount) as total, MAX(amount) as max_amt, AVG(amount) as avg_amt").
	Group("user_id")

db.Table("(?) as order_stats", subQuery).
	Select("users.name as user_name, order_stats.cnt as order_count, "+
		"order_stats.total as total_spent, order_stats.max_amt as max_amount, "+
		"order_stats.avg_amt as avg_amount").
	Joins("JOIN users ON users.id = order_stats.user_id").
	Order("total_spent desc").
	Limit(20).
	Scan(&reports)

// 输出消费最高的前 20 名用户
for i, r := range reports {
	fmt.Printf("%d. %s: %d笔订单, 总额%.2f元, 最高%.2f元, 平均%.2f元\n",
		i+1, r.UserName, r.OrderCount, r.TotalSpent, r.MaxAmount, r.AvgAmount)
}

---

常见错误

错误1：子查询返回多列

// ❌ WHERE IN 子查询只能返回一列
db.Where("id IN (?)", db.Model(&Order{}).Select("user_id, amount"))
// 错误！子查询不能返回多列

// ✅ 只返回一列
db.Where("id IN (?)", db.Model(&Order{}).Select("user_id"))

错误2：子查询性能问题

// ❌ 关联子查询每行都执行一次，数据量大时极慢
db.Where("amount > (SELECT AVG(amount) FROM orders WHERE user_id = ?)", userID)

// 好的替代：先查平均值，再查数据
var avgAmount float64
db.Model(&Order{}).Where("user_id = ?", userID).Select("AVG(amount)").Scan(&avgAmount)
db.Where("amount > ?", avgAmount).Find(&orders)

错误3：Rows() 忘记 Close()

// ❌ 忘记 defer rows.Close()
rows, _ := db.Model(&User{}).Rows()
for rows.Next() {
	// ...
}
// 连接泄漏！rows 没有关闭

// ✅ defer rows.Close()
rows, _ := db.Model(&User{}).Rows()
defer rows.Close()
for rows.Next() { ... }

---

本章小结

• WHERE 子查询：db.Where("col > (?)", subQuery)
• FROM 子查询（派生表）：db.Table("(?) as alias", subQuery)
• SELECT 子查询：在 Select 字符串中直接写 (SELECT ...)
• Rows() 逐行处理大结果集，记得 defer rows.Close()
• 命名参数 @name 让长 SQL 更清晰
• 子查询虽灵活，但要注意性能（关联子查询是性能杀手）

练习题

1. 用子查询查出"订单总金额大于所有用户平均订单总金额"的用户。
2. 用 FROM 子查询实现：按城市统计用户数，然后筛选人数 > 10 的城市。
3. 用 SELECT 子查询实现：在用户列表中显示每个用户的订单数。
4. 使用 Rows() 处理一个假的大结果集，体验逐行处理。
5. 用命名参数重写一个多参数的 WHERE 查询。
6. （思考题）为什么关联子查询（每行执行一次子查询）性能很差？有什么优化思路？