无需groupBy查询多列聚合总和 兼容MySQL only_full_group_by模式

错误原因说明 #

你原来的写法主查询直接针对transactions全表，添加了多个聚合子查询但主查询本身没有指定全局聚合逻辑，触发了MySQL only_full_group_by 模式的校验规则。

方案1：条件聚合（推荐，性能最优，仅扫描一次表） #

该写法完全符合SQL规范，不需要修改配置，执行效率远高于子查询方案：

$result = DB::table('transactions')
    ->selectRaw("
        SUM(IF(type = 'top_up', credit_movement, 0)) as top_up,
        SUM(IF(type = 'deduct', credit_movement, 0)) as deduct,
        SUM(IF(type = 'fee', credit_movement, 0)) as fee,
        SUM(IF(type = 'top_up', credit_movement, 0)) + SUM(IF(type = 'deduct', credit_movement, 0)) + SUM(IF(type = 'fee', credit_movement, 0)) as net_expense
    ")
    ->first();

执行后返回的就是你需要的单条汇总结果。如果需要保留你原来的$types动态配置，可以用下面的通用写法：

$types = [
   'top_up' => ['top_up'],
   'deduct' => ['deduct'],
   'fee' => ['fee'],
   'net_expense' => ['top_up', 'deduct', 'fee'], 
];

$selects = [];
$bindings = [];
foreach ($types as $alias => $includeTypes) {
    $placeholders = implode(',', array_fill(0, count($includeTypes), '?'));
    $selects[] = "SUM(IF(type IN ($placeholders), credit_movement, 0)) as $alias";
    $bindings = array_merge($bindings, $includeTypes);
}

$result = DB::table('transactions')
    ->selectRaw(implode(',', $selects), $bindings)
    ->first();

方案2：修复原有的子查询写法 #

如果你不想改子查询的实现逻辑，只需要把主查询改成虚拟表查询，避免直接查transactions触发分组校验即可：

$types = [
   'top_up' => ['top_up'],
   'deduct' => ['deduct'],
   'fee' => ['fee'],
   'net_expense' => ['top_up', 'deduct', 'fee'], 
];

// 主查询用空查询作为虚拟表，不直接关联transactions
$query = DB::query();
foreach ($types as $type => $fields) {
    $query->selectSub(function ($q) use ($fields) {
        return $q->from('transactions')
            ->selectRaw('SUM(credit_movement)')
            ->whereIn('type', $fields);
    }, $type);
};

$result = $query->first();

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