GORM子查询操作

子查询 子查询（Subquery）是SQL中非常强大的功能，它允许嵌套查询。 当你使用 *gorm.DB 对象作为参数时，GORM 可以自动生成子查询。

// 简单的子查询 db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders) // SQL: SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");

// 内嵌子查询 subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users") db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results) // SQL: SELECT AVG(age) as avgage FROM users GROUP BY name HAVING AVG(age) > (SELECT AVG(age) FROM users WHERE name LIKE "name%") From 子查询 GORM 允许在 FROM 子句中使用子查询，从而支持复杂的查询和数据组织。

// 在 FROM 子句中使用子查询 db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18).Find(&User{}) // SQL: SELECT * FROM (SELECT name,age FROM users) as u WHERE age = 18

// 在 FROM 子句中结合多个子查询 subQuery1 := db.Model(&User{}).Select("name") subQuery2 := db.Model(&Pet{}).Select("name") db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{}) // SQL: SELECT * FROM (SELECT name FROM users) as u, (SELECT name FROM pets) as p

go
package main

import (
    "fmt"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type WealthOrder struct {
    Currency    string
    ApplyAmount float64
}

func main() {
    // 连接数据库（以SQLite为例，您可能需要根据实际情况调整）
    db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    // 构建子查询SQL
    subQuerySQL := db.Model(&WealthOrder{}).
        Select("currency, applyAmount").
        Where("orderStatus IN ?", []int{4, 100, 200, 300, 400, 450, 500, 501, 600, 601, 602, 700, 800, 900}).
        Order("submitTime DESC").
        Limit(2).
        Offset(1).
        ToSQL(func(tx *gorm.DB) *gorm.DB {
            return tx.Select("currency, SUM(applyAmount) AS totalAmount")
        })

    // 使用子查询
    var results []map[string]interface{}
    db.Raw("SELECT currency, SUM(applyAmount) AS totalAmount FROM (?) AS todr GROUP BY currency", gorm.Expr(subQuerySQL)).Scan(&results)

    // 打印结果
    fmt.Println(results)
}