Laravel+PostgreSQL中如何用GROUP BY结合CASE实现聚合统计？

我有一张financial_transactions表，结构如下：

id | user_id | amount | type
----------------------------
1  | 1       | 10     | 1
2  | 1       | 20     | 2
3  | 2       | 15     | 1
4  | 2       | 10     | 1

需要按用户分组，计算每个用户的存款（type=1）和取款（type=2）总额，预期输出：

[
    [
        'user_id' => 1,
        'totalDeposit' => 10,
        'totalWithdraw' => 20
    ],
    [
        'user_id' => 2,
        'totalDeposit' => 25,
        'totalWithdraw' => 0
    ]
]

当前使用的Laravel Eloquent代码：

$data = FinancialTransaction::select([
        DB::raw("CASE WHEN (type = '1') THEN SUM(amount) ELSE 0 END AS totalDeposit"),
        DB::raw("CASE WHEN (type = '2') THEN SUM(amount) ELSE 0 END AS totalWithdraw"),
    ])->groupBy('user_id','type')
    ->get();

实际输出结果：

[
    [
        'user_id' => 1,
        'totalDeposit' => 10,
        'totalWithdraw' => 0
    ],
    [
        'user_id' => 1,
        'totalDeposit' => 0,
        'totalWithdraw' => 20
    ],
    [
        'user_id' => 2,
        'totalDeposit' => 25,
        'totalWithdraw' => 0
    ]
]

如果从groupBy中移除type，会报以下错误：

type must appear in the GROUP BY clause or be used in an aggregate function

解决方案 #

问题出在你把SUM()放在了CASE内部，并且按type分组，导致每个用户的不同交易类型被拆分成单独行。正确的做法是把CASE放在SUM()内部，这样就能在按user_id分组时，分别统计不同类型的金额总和，同时避免type字段的分组问题。

正确的Eloquent代码： #

$data = FinancialTransaction::select([
        'user_id',
        DB::raw("SUM(CASE WHEN type = '1' THEN amount ELSE 0 END) AS totalDeposit"),
        DB::raw("SUM(CASE WHEN type = '2' THEN amount ELSE 0 END) AS totalWithdraw"),
    ])->groupBy('user_id')
    ->get();

代码说明： #

1. 将CASE表达式作为SUM()的参数，先判断每条记录的type，再把符合条件的amount累加到对应总额中。
2. 仅按user_id分组，确保每个用户只返回一行数据。
3. 显式选择user_id字段，避免分组时的字段缺失问题。

执行这段代码后，就能得到预期输出：每个用户一行，包含存款总额和取款总额，无对应交易的类型会显示0。

内容的提问来源于stack exchange，提问作者Dito Khelaia