05-查询进阶：Scope、SubQuery 与 SQL 构建

基础 CRUD 只能应付简单场景，真实业务中往往涉及复杂的查询逻辑：可复用的查询条件、子查询、多表 JOIN、窗口函数、CTE 等。GORM 提供了 Scope 机制复用查询、子查询构建器、原生 SQL 接口以及强大的条件组合能力。本篇将深入这些进阶查询技术。

Scope：可复用查询条件

Scope 是一个接收 *gorm.DB 并返回 *gorm.DB 的函数，用于封装可复用的查询逻辑。通过 Scopes 方法可以组合多个 Scope。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID     uint `gorm:"primaryKey"`
	Name   string
	Age    int
	Status int
	City   string
}

// Paginate 分页 Scope
func Paginate(page, size int) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if page <= 0 {
			page = 1
		}
		if size <= 0 || size > 100 {
			size = 10
		}
		offset := (page - 1) * size
		return db.Offset(offset).Limit(size)
	}
}

// Active 只查启用用户
func Active(db *gorm.DB) *gorm.DB {
	return db.Where("status = ?", 1)
}

// Adult 只查成年人
func Adult(db *gorm.DB) *gorm.DB {
	return db.Where("age >= ?", 18)
}

// ByCity 按城市过滤
func ByCity(city string) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Where("city = ?", city)
	}
}

func main() {
	db, err := gorm.Open(sqlite.Open("scope.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{})

	// 准备数据
	for i := 1; i <= 20; i++ {
		status := 1
		if i%5 == 0 {
			status = 0
		}
		city := "Beijing"
		if i%2 == 0 {
			city = "Shanghai"
		}
		db.Create(&User{
			Name:   fmt.Sprintf("user_%d", i),
			Age:    15 + i,
			Status: status,
			City:   city,
		})
	}

	// 组合多个 Scope：活跃的成年北京用户，第 1 页每页 5 条
	var users []User
	db.Scopes(Active, Adult, ByCity("Beijing"), Paginate(1, 5)).Find(&users)
	fmt.Printf("组合 Scope 查询: %d 条\n", len(users))
	for _, u := range users {
		fmt.Printf("  - %s (age=%d, city=%s, status=%d)\n",
			u.Name, u.Age, u.City, u.Status)
	}

	// 计算总数
	var total int64
	db.Model(&User{}).Scopes(Active, Adult, ByCity("Beijing")).Count(&total)
	fmt.Printf("总数: %d\n", total)
}

Scope 的优势：

复用：通用查询逻辑封装一次到处使用
组合：多个 Scope 自由组合，灵活度高
可读性：业务意图清晰，例如 Active.Adult.ByCity("Beijing")

子查询：SubQuery

GORM 支持在 WHERE、FROM、SELECT 等位置使用子查询。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Age  int
	City string
}

type Order struct {
	ID      uint `gorm:"primaryKey"`
	UserID  uint
	Amount  float64
}

func main() {
	db, err := gorm.Open(sqlite.Open("sub.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{}, &Order{})

	// 准备数据
	users := []User{
		{Name: "A", Age: 25, City: "Beijing"},
		{Name: "B", Age: 30, City: "Shanghai"},
		{Name: "C", Age: 28, City: "Beijing"},
	}
	db.Create(&users)
	db.Create(&Order{UserID: 1, Amount: 100})
	db.Create(&Order{UserID: 1, Amount: 200})
	db.Create(&Order{UserID: 2, Amount: 150})

	// 子查询：查询有订单的用户
	var usersWithOrders []User
	subQuery := db.Model(&Order{}).Select("DISTINCT user_id")
	db.Where("id IN (?)", subQuery).Find(&usersWithOrders)
	fmt.Printf("有订单的用户数: %d\n", len(usersWithOrders))

	// 子查询：查询比平均年龄大的用户
	var avgAge int
	db.Model(&User{}).Select("AVG(age)").Scan(&avgAge)
	var olderUsers []User
	db.Where("age > (?)", db.Model(&User{}).Select("AVG(age)")).Find(&olderUsers)
	fmt.Printf("比平均年龄(%d)大的用户数: %d\n", avgAge, len(olderUsers))

	// FROM 子查询
	type Result struct {
		City   string
		Total  float64
	}
	var results []Result
	db.Table("(?) as uo",
		db.Table("users").
			Select("users.id, users.city, orders.amount").
			Joins("LEFT JOIN orders ON orders.user_id = users.id"),
	).Select("city, SUM(amount) as total").
		Group("city").
		Scan(&results)
	fmt.Println("各城市订单总额:")
	for _, r := range results {
		fmt.Printf("  %s: %.0f\n", r.City, r.Total)
	}
}

原生 SQL：Raw、Exec

对于复杂查询或 GORM 难以表达的场景，可以使用原生 SQL。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Age  int
	City string
}

type Order struct {
	ID      uint `gorm:"primaryKey"`
	UserID  uint
	Amount  float64
	Status  string
}

func main() {
	db, err := gorm.Open(sqlite.Open("raw.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{}, &Order{})

	db.Create(&User{Name: "Tom", Age: 25, City: "Beijing"})
	db.Create(&User{Name: "Jerry", Age: 30, City: "Shanghai"})
	db.Create(&Order{UserID: 1, Amount: 100, Status: "paid"})
	db.Create(&Order{UserID: 1, Amount: 200, Status: "paid"})
	db.Create(&Order{UserID: 2, Amount: 150, Status: "pending"})

	// Raw 查询映射到结构体
	type UserOrderCount struct {
		Name  string
		Count int
		Total float64
	}
	var results []UserOrderCount
	db.Raw(`
		SELECT u.name, COUNT(o.id) as count, COALESCE(SUM(o.amount), 0) as total
		FROM users u
		LEFT JOIN orders o ON o.user_id = u.id AND o.status = ?
		GROUP BY u.id, u.name
	`, "paid").Scan(&results)
	fmt.Println("用户已支付订单统计:")
	for _, r := range results {
		fmt.Printf("  %s: %d 单, 总额 %.0f\n", r.Name, r.Count, r.Total)
	}

	// Raw 查询单行
	var name string
	db.Raw("SELECT name FROM users WHERE age > ?", 26).Scan(&name)
	fmt.Printf("年龄大于26的用户: %s\n", name)

	// Raw 查询单值
	var count int
	db.Raw("SELECT COUNT(*) FROM users").Scan(&count)
	fmt.Printf("用户总数: %d\n", count)

	// Exec 执行不返回行的 SQL（更新、删除、DDL）
	result := db.Exec("UPDATE users SET city = ? WHERE name = ?", "Guangzhou", "Tom")
	fmt.Printf("Exec 更新行数: %d\n", result.RowsAffected)
}

SQL 构建器：条件组合

GORM 提供了 And、Or、Not 等方法组合复杂条件。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID     uint `gorm:"primaryKey"`
	Name   string
	Age    int
	Status int
	City   string
}

func main() {
	db, err := gorm.Open(sqlite.Open("cond.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{})

	db.Create(&User{Name: "A", Age: 25, Status: 1, City: "Beijing"})
	db.Create(&User{Name: "B", Age: 30, Status: 0, City: "Shanghai"})
	db.Create(&User{Name: "C", Age: 28, Status: 1, City: "Beijing"})
	db.Create(&User{Name: "D", Age: 35, Status: 0, City: "Shenzhen"})

	var users []User

	// Not
	db.Not("city = ?", "Beijing").Find(&users)
	fmt.Printf("Not city=Beijing: %d 条\n", len(users))

	// Or
	db.Where("city = ?", "Beijing").Or("city = ?", "Shanghai").Find(&users)
	fmt.Printf("city=Beijing OR Shanghai: %d 条\n", len(users))

	// 复杂组合：(city=Beijing AND status=1) OR (age > 32)
	db.Where(
		db.Where("city = ?", "Beijing").Where("status = ?", 1),
	).Or("age > ?", 32).Find(&users)
	fmt.Printf("复杂组合: %d 条\n", len(users))
	for _, u := range users {
		fmt.Printf("  - %s (city=%s, status=%d, age=%d)\n",
			u.Name, u.City, u.Status, u.Age)
	}

	// Not + 多条件
	db.Not("status = ? AND city = ?", 1, "Beijing").Find(&users)
	fmt.Printf("Not (status=1 AND city=Beijing): %d 条\n", len(users))
}

命名参数

GORM 支持使用 @name 形式的命名参数，提高 SQL 可读性。

package main

import (
	"database/sql"
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Age  int
	City string
}

func main() {
	db, err := gorm.Open(sqlite.Open("named.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{})

	db.Create(&User{Name: "Tom", Age: 25, City: "Beijing"})
	db.Create(&User{Name: "Jerry", Age: 30, City: "Shanghai"})

	var users []User

	// 命名参数
	db.Where("city = @city AND age > @age", sql.Named("city", "Beijing"), sql.Named("age", 20)).Find(&users)
	// 或者用 map 形式
	db.Where("city = @city AND age > @age", map[string]interface{}{
		"city": "Beijing",
		"age":  20,
	}).Find(&users)
	fmt.Printf("命名参数查询: %d 条\n", len(users))

	// Raw 中使用命名参数
	db.Raw("SELECT * FROM users WHERE city = @city",
		sql.Named("city", "Shanghai")).Scan(&users)
	fmt.Printf("Raw 命名参数: %d 条\n", len(users))
}

命名参数使用 sql.Named 或 map[string]interface{} 两种方式传入，避免占位符顺序错误。

查询优化：使用索引提示

部分数据库支持索引提示（Index Hint），强制使用某个索引以优化查询。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID    uint   `gorm:"primaryKey"`
	Name  string `gorm:"index"`
	Email string `gorm:"uniqueIndex"`
	Age   int    `gorm:"index"`
	City  string `gorm:"index:idx_city_age,priority:1;index:idx_city_age,priority:2,composite:age"`
}

func main() {
	db, err := gorm.Open(sqlite.Open("hint.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{})

	// 准备数据
	for i := 1; i <= 100; i++ {
		db.Create(&User{
			Name:  fmt.Sprintf("user_%d", i),
			Email: fmt.Sprintf("user_%d@x.com", i),
			Age:   20 + i%50,
			City:  []string{"Beijing", "Shanghai", "Shenzhen"}[i%3],
		})
	}

	// MySQL 索引提示
	// db.Clauses(hints.UseIndex("idx_user_name")).Find(&users)
	// db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_age").ForJoin()).Find(&users)

	// 使用解释计划查看是否命中索引
	var users []User
	result := db.Where("city = ? AND age > ?", "Beijing", 30).Find(&users)
	fmt.Printf("查询到 %d 条, 行数: %d\n", len(users), result.RowsAffected)

	// 查看 SQL
	fmt.Println("生成 SQL:", result.Statement.SQL.String())
}

复杂查询案例

多表 JOIN 查询

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	City string
}

type Order struct {
	ID      uint `gorm:"primaryKey"`
	UserID  uint
	Amount  float64
	Status  string
}

type Product struct {
	ID    uint `gorm:"primaryKey"`
	Name  string
	Price float64
}

type OrderItem struct {
	ID        uint `gorm:"primaryKey"`
	OrderID   uint
	ProductID uint
	Quantity  int
}

func main() {
	db, err := gorm.Open(sqlite.Open("join.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{}, &Order{}, &Product{}, &OrderItem{})

	// 准备数据
	db.Create(&User{Name: "Tom", City: "Beijing"})
	db.Create(&User{Name: "Jerry", City: "Shanghai"})
	db.Create(&Product{Name: "Book", Price: 50})
	db.Create(&Product{Name: "Pen", Price: 10})
	db.Create(&Order{UserID: 1, Amount: 100, Status: "paid"})
	db.Create(&Order{UserID: 2, Amount: 60, Status: "paid"})
	db.Create(&OrderItem{OrderID: 1, ProductID: 1, Quantity: 2})
	db.Create(&OrderItem{OrderID: 1, ProductID: 2, Quantity: 0})
	db.Create(&OrderItem{OrderID: 2, ProductID: 2, Quantity: 6})

	// 多表 JOIN：查询每个用户购买的商品和数量
	type OrderDetail struct {
		UserName    string
		City        string
		ProductName string
		Quantity    int
		Amount      float64
	}
	var details []OrderDetail
	db.Table("orders").
		Select("users.name as user_name, users.city, products.name as product_name, order_items.quantity, orders.amount").
		Joins("LEFT JOIN users ON users.id = orders.user_id").
		Joins("LEFT JOIN order_items ON order_items.order_id = orders.id").
		Joins("LEFT JOIN products ON products.id = order_items.product_id").
		Where("orders.status = ? AND order_items.quantity > ?", "paid", 0).
		Scan(&details)
	fmt.Println("订单详情:")
	for _, d := range details {
		fmt.Printf("  %s(%s) 购买 %s x%d, 订单金额 %.0f\n",
			d.UserName, d.City, d.ProductName, d.Quantity, d.Amount)
	}

	// 使用 GORM 模型 Joins
	var orders []Order
	db.Joins("JOIN users ON users.id = orders.user_id AND users.city = ?", "Beijing").
		Find(&orders)
	fmt.Printf("北京用户订单数: %d\n", len(orders))
}

EXISTS 子查询

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
}

type Order struct {
	ID      uint `gorm:"primaryKey"`
	UserID  uint
	Amount  float64
}

func main() {
	db, err := gorm.Open(sqlite.Open("exists.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{}, &Order{})

	db.Create(&User{Name: "Tom"})
	db.Create(&User{Name: "Jerry"})
	db.Create(&User{Name: "Spike"})
	db.Create(&Order{UserID: 1, Amount: 100})
	db.Create(&Order{UserID: 2, Amount: 200})

	// 查询有订单的用户（EXISTS）
	var usersWithOrders []User
	db.Where("EXISTS (?)",
		db.Table("orders").Select("1").Where("orders.user_id = users.id"),
	).Find(&usersWithOrders)
	fmt.Printf("有订单的用户数: %d\n", len(usersWithOrders))
	for _, u := range usersWithOrders {
		fmt.Printf("  - %s\n", u.Name)
	}

	// NOT EXISTS：没有订单的用户
	var usersWithoutOrders []User
	db.Where("NOT EXISTS (?)",
		db.Table("orders").Select("1").Where("orders.user_id = users.id"),
	).Find(&usersWithoutOrders)
	fmt.Printf("无订单的用户数: %d\n", len(usersWithoutOrders))
	for _, u := range usersWithoutOrders {
		fmt.Printf("  - %s\n", u.Name)
	}
}

窗口函数

窗口函数用于在结果集上执行聚合计算而不分组。MySQL 8+、PostgreSQL 等支持。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type Sale struct {
	ID       uint `gorm:"primaryKey"`
	Salesman string
	Month    string
	Amount   float64
}

func main() {
	db, err := gorm.Open(sqlite.Open("window.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&Sale{})

	sales := []Sale{
		{Salesman: "Tom", Month: "2024-01", Amount: 100},
		{Salesman: "Tom", Month: "2024-02", Amount: 150},
		{Salesman: "Tom", Month: "2024-03", Amount: 120},
		{Salesman: "Jerry", Month: "2024-01", Amount: 200},
		{Salesman: "Jerry", Month: "2024-02", Amount: 180},
		{Salesman: "Jerry", Month: "2024-03", Amount: 220},
	}
	db.Create(&sales)

	// ROW_NUMBER：按销售员分组，金额降序排名
	type Result struct {
		Salesman string
		Month    string
		Amount   float64
		Rank     int
	}
	var results []Result
	db.Raw(`
		SELECT
			salesman, month, amount,
			ROW_NUMBER() OVER (PARTITION BY salesman ORDER BY amount DESC) as rank
		FROM sales
		ORDER BY salesman, rank
	`).Scan(&results)
	fmt.Println("各销售员业绩排名:")
	for _, r := range results {
		fmt.Printf("  %s %s: %.0f (排名 %d)\n", r.Salesman, r.Month, r.Amount, r.Rank)
	}

	// 累计求和
	type Cumulative struct {
		Salesman   string
		Month      string
		Amount     float64
		Cumulative float64
	}
	var cumul []Cumulative
	db.Raw(`
		SELECT
			salesman, month, amount,
			SUM(amount) OVER (PARTITION BY salesman ORDER BY month) as cumulative
		FROM sales
		ORDER BY salesman, month
	`).Scan(&cumul)
	fmt.Println("累计销售额:")
	for _, c := range cumul {
		fmt.Printf("  %s %s: %.0f (累计 %.0f)\n", c.Salesman, c.Month, c.Amount, c.Cumulative)
	}
}

CTE (Common Table Expression)

CTE 即公用表表达式，通过 WITH 子句创建临时结果集，可大幅简化复杂查询。注意 SQLite 默认不支持 CTE 递归，但支持普通 CTE。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type Employee struct {
	ID       uint `gorm:"primaryKey"`
	Name     string
	ManagerID *uint
	Salary   float64
}

func main() {
	db, err := gorm.Open(sqlite.Open("cte.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&Employee{})

	// 准备层级数据
	ceo := Employee{Name: "CEO", Salary: 50000}
	db.Create(&ceo)
	mgrID := ceo.ID
	mgr := Employee{Name: "Manager", ManagerID: &mgrID, Salary: 30000}
	db.Create(&mgr)
	m1ID := mgr.ID
	db.Create(&Employee{Name: "Tom", ManagerID: &m1ID, Salary: 10000})
	db.Create(&Employee{Name: "Jerry", ManagerID: &m1ID, Salary: 12000})

	// 普通 CTE：高薪员工
	type Result struct {
		Name   string
		Salary float64
	}
	var results []Result
	db.Raw(`
		WITH high_paid AS (
			SELECT name, salary FROM employees WHERE salary > ?
		)
		SELECT * FROM high_paid ORDER BY salary DESC
	`, 15000).Scan(&results)
	fmt.Println("高薪员工:")
	for _, r := range results {
		fmt.Printf("  %s: %.0f\n", r.Name, r.Salary)
	}

	// 递归 CTE：查询所有下属（含自身）
	type Hierarchy struct {
		ID     uint
		Name   string
		Level  int
	}
	var hierarchy []Hierarchy
	db.Raw(`
		WITH RECURSIVE org_chart AS (
			SELECT id, name, 0 as level FROM employees WHERE manager_id IS NULL
			UNION ALL
			SELECT e.id, e.name, oc.level + 1
			FROM employees e
			JOIN org_chart oc ON e.manager_id = oc.id
		)
		SELECT * FROM org_chart ORDER BY level
	`).Scan(&hierarchy)
	fmt.Println("组织架构:")
	for _, h := range hierarchy {
		fmt.Printf("  %s%s (level %d)\n", "", h.Name, h.Level)
	}
}

Explain 分析查询计划

Explain 方法用于查看 SQL 执行计划，分析查询性能。

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID    uint   `gorm:"primaryKey"`
	Name  string `gorm:"index"`
	Email string `gorm:"uniqueIndex"`
	Age   int
	City  string `gorm:"index"`
}

func main() {
	db, err := gorm.Open(sqlite.Open("explain.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{})

	for i := 1; i <= 100; i++ {
		db.Create(&User{
			Name:  fmt.Sprintf("user_%d", i),
			Email: fmt.Sprintf("user_%d@x.com", i),
			Age:   20 + i%50,
			City:  []string{"Beijing", "Shanghai"}[i%2],
		})
	}

	// Explain 查看执行计划
	var result string
	db.Model(&User{}).Where("city = ? AND age > ?", "Beijing", 30).Explain().Scan(&result)
	fmt.Println("执行计划:")
	fmt.Println(result)

	// 复杂查询的 Explain
	db.Model(&User{}).
		Where("city = ?", "Beijing").
		Order("age desc").
		Limit(10).
		Explain(true) // true 表示打印完整 SQL
}

完整示例：报表统计查询

下面用一个报表统计查询综合运用 Scope、子查询、JOIN、聚合等技术。

package main

import (
	"fmt"
	"log"
	"time"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID       uint `gorm:"primaryKey"`
	Name     string
	City     string
	JoinDate time.Time
}

type Order struct {
	ID         uint `gorm:"primaryKey"`
	UserID     uint
	Amount     float64
	Status     string
	CategoryID uint
	CreatedAt  time.Time
}

type Category struct {
	ID   uint `gorm:"primaryKey"`
	Name string
}

// Scopes 复用查询条件
func PaidOrders(db *gorm.DB) *gorm.DB {
	return db.Where("status = ?", "paid")
}

func LastNDays(days int) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Where("created_at >= ?", time.Now().AddDate(0, 0, -days))
	}
}

func main() {
	db, err := gorm.Open(sqlite.Open("report.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}
	db.AutoMigrate(&User{}, &Order{}, &Category{})

	// 准备数据
	db.Create(&User{Name: "Tom", City: "Beijing", JoinDate: time.Now().AddDate(0, -2, 0)})
	db.Create(&User{Name: "Jerry", City: "Shanghai", JoinDate: time.Now().AddDate(0, -1, 0)})
	db.Create(&User{Name: "Spike", City: "Beijing", JoinDate: time.Now().AddDate(0, -3, 0)})

	db.Create(&Category{Name: "电子产品"})
	db.Create(&Category{Name: "图书"})
	db.Create(&Category{Name: "服装"})

	now := time.Now()
	db.Create(&Order{UserID: 1, Amount: 100, Status: "paid", CategoryID: 1, CreatedAt: now.AddDate(0, 0, -5)})
	db.Create(&Order{UserID: 1, Amount: 200, Status: "paid", CategoryID: 2, CreatedAt: now.AddDate(0, 0, -3)})
	db.Create(&Order{UserID: 2, Amount: 150, Status: "paid", CategoryID: 1, CreatedAt: now.AddDate(0, 0, -2)})
	db.Create(&Order{UserID: 2, Amount: 80, Status: "pending", CategoryID: 3, CreatedAt: now.AddDate(0, 0, -1)})
	db.Create(&Order{UserID: 3, Amount: 300, Status: "paid", CategoryID: 2, CreatedAt: now.AddDate(0, 0, -10)})

	// 报表1：各城市销售额
	type CityReport struct {
		City   string
		Total  float64
		Count  int64
	}
	var cityReports []CityReport
	db.Model(&Order{}).
		Scopes(PaidOrders).
		Select("users.city as city, SUM(orders.amount) as total, COUNT(*) as count").
		Joins("JOIN users ON users.id = orders.user_id").
		Group("users.city").
		Scan(&cityReports)
	fmt.Println("=== 各城市销售额 ===")
	for _, r := range cityReports {
		fmt.Printf("  %s: %.0f (%d单)\n", r.City, r.Total, r.Count)
	}

	// 报表2：各品类销售额
	type CategoryReport struct {
		Category string
		Total    float64
	}
	var catReports []CategoryReport
	db.Model(&Order{}).
		Scopes(PaidOrders).
		Select("categories.name as category, SUM(orders.amount) as total").
		Joins("JOIN categories ON categories.id = orders.category_id").
		Group("categories.id, categories.name").
		Scan(&catReports)
	fmt.Println("\n=== 各品类销售额 ===")
	for _, r := range catReports {
		fmt.Printf("  %s: %.0f\n", r.Category, r.Total)
	}

	// 报表3：高消费用户（消费额高于平均值）
	type TopUser struct {
		Name   string
		Total  float64
	}
	var topUsers []TopUser
	db.Raw(`
		SELECT u.name, SUM(o.amount) as total
		FROM users u
		JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
		GROUP BY u.id, u.name
		HAVING SUM(o.amount) > (
			SELECT AVG(total) FROM (
				SELECT SUM(amount) as total
				FROM orders
				WHERE status = 'paid'
				GROUP BY user_id
			)
		)
		ORDER BY total DESC
	`).Scan(&topUsers)
	fmt.Println("\n=== 高消费用户 ===")
	for _, u := range topUsers {
		fmt.Printf("  %s: %.0f\n", u.Name, u.Total)
	}

	// 报表4：使用 Scope 组合查询最近 7 天已支付订单
	var recentOrders []Order
	db.Scopes(PaidOrders, LastNDays(7)).Find(&recentOrders)
	fmt.Printf("\n=== 最近7天已支付订单 ===\n共 %d 单\n", len(recentOrders))
}

小结

本篇系统讲解了 GORM 的进阶查询能力：

Scope：封装可复用查询条件，支持自由组合，提升代码复用性和可读性
子查询：WHERE、FROM、SELECT 等位置均支持子查询
原生 SQL：Raw 查询、Exec 执行，应对复杂场景
条件组合：And、Or、Not 灵活组合复杂条件
命名参数：@name 形式提高 SQL 可读性
索引提示：通过 Clauses 强制使用特定索引
多表 JOIN：链式 Joins 实现多表关联查询
EXISTS 子查询：存在性检查，比 IN 更高效
窗口函数：ROW_NUMBER、SUM OVER 等高级分析函数
CTE：公用表表达式，简化复杂查询，支持递归
Explain：分析执行计划，定位性能问题

下一篇我们将学习迁移与索引管理，掌握数据库结构版本化管理的最佳实践。

入门篇

进阶篇

资深篇

05-查询进阶：Scope、SubQuery 与 SQL 构建

Scope：可复用查询条件

子查询：SubQuery

原生 SQL：Raw、Exec

SQL 构建器：条件组合

命名参数

查询优化：使用索引提示

复杂查询案例

多表 JOIN 查询

EXISTS 子查询

窗口函数

CTE (Common Table Expression)

Explain 分析查询计划

完整示例：报表统计查询

小结

05-查询进阶：Scope、SubQuery 与 SQL 构建 ​

Scope：可复用查询条件 ​

子查询：SubQuery ​

原生 SQL：Raw、Exec ​

SQL 构建器：条件组合 ​

命名参数 ​

查询优化：使用索引提示 ​

复杂查询案例 ​

多表 JOIN 查询 ​

EXISTS 子查询 ​

窗口函数 ​

CTE (Common Table Expression) ​

Explain 分析查询计划 ​

完整示例：报表统计查询 ​

小结 ​

05-查询进阶：Scope、SubQuery 与 SQL 构建

Scope：可复用查询条件

子查询：SubQuery

原生 SQL：Raw、Exec

SQL 构建器：条件组合

命名参数

查询优化：使用索引提示

复杂查询案例

多表 JOIN 查询

EXISTS 子查询

窗口函数

CTE (Common Table Expression)

Explain 分析查询计划

完整示例：报表统计查询

小结