如何使用GORM实现按状态拆分金额的条件聚合查询？

需求说明 #

需要执行条件聚合操作，将某列数值按不同状态拆分求和到不同列中。

原始数据 #

期望统计结果 #

已实现的原生SQL #

SELECT
  sum(amount) FILTER (WHERE status = 'confirmed') AS confirmed,
  sum(amount) FILTER (WHERE status = 'unconfirmed') AS unconfirmed,
  sum(amount) AS total
FROM table_name
WHERE id = 1

请问如何使用GORM实现上述查询？

GORM实现方案 #

方法1：用Select构造查询 #

直接在Select中编写聚合表达式，配合Where过滤条件，用结构体接收结果：

首先定义接收结果的结构体：

type StatsResult struct {
    Confirmed   float64 `gorm:"column:confirmed"`
    Unconfirmed float64 `gorm:"column:unconfirmed"`
    Total       float64 `gorm:"column:total"`
}

执行查询：

var result StatsResult
db.Table("table_name").
    Select(`sum(amount) FILTER (WHERE status = 'confirmed') AS confirmed,
            sum(amount) FILTER (WHERE status = 'unconfirmed') AS unconfirmed,
            sum(amount) AS total`).
    Where("id = ?", 1).
    Scan(&result)

方法2：直接执行原生SQL #

复用已有的原生SQL，用GORM的Raw方法执行：

var result StatsResult
db.Raw(`SELECT
            sum(amount) FILTER (WHERE status = 'confirmed') AS confirmed,
            sum(amount) FILTER (WHERE status = 'unconfirmed') AS unconfirmed,
            sum(amount) AS total
        FROM table_name
        WHERE id = ?`, 1).Scan(&result)

方法3：兼容非PostgreSQL数据库的写法 #

如果数据库不支持FILTER语法（如MySQL），用CASE WHEN替代，GORM同样支持：

var result StatsResult
db.Table("table_name").
    Select(`sum(CASE WHEN status = 'confirmed' THEN amount ELSE 0 END) AS confirmed,
            sum(CASE WHEN status = 'unconfirmed' THEN amount ELSE 0 END) AS unconfirmed,
            sum(amount) AS total`).
    Where("id = ?", 1).
    Scan(&result)

内容的提问来源于stack exchange，提问作者Roberto Solares