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 构建子查询:
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();