sum 加 Group By 的使用

1、A 表 有server_id  为1的两条数据

2、B表有server_id 为1的数据也有两条

原本Sql是这样   乍一看没啥问题  但是group by 没生效 

SELECT a.server_id,sum(b.score) as score FROM `a` INNER JOIN `b` ON `a`.`server_id`=`b`.`server_id` WHERE  `b`.`server_id` = 1 GROUP BY `a`.`server_id` LIMIT 1

期望的值是 20 返回结果却是 40

修改Sql 构建子查询：

 

distinct说明：用于返回唯一不同的值。

select a.server_id, sum(b.score) from ( select distinct a.server_id from a)a inner join b on a.server_id = b.server_id group by a.server_id

Tp写法：

// 构建子查询
        $sql = Db::name('a')->field('distinct server_id')->buildSql();
        

        $r = Db::table($sql.'a')
            
            ->join('b','a.server_id = b.server_id')
            
            ->where('b.server_id', 1)
            
            ->field('a.server_id,sum(b.score) as score')
            
            //->group('a.server_id')
            
            ->find();

Laravel写法：

        $build_sql = DB::table('a')
            ->select('server_id')
            ->groupBy('ServerID');

        $r = DB::table( DB::raw("({$build_sql->toSql()}) as a") )

            ->join('b', 'a.server_id', 'b.server_id')

            ->whereDate('a.CreateTime', date('Y-m-d'))

            ->selectRaw('sum(b.score) as score')

            ->where('a.server_id', 1)

            ->first();