mysql - Mysql 中 DATE_FORMAT 的 groupby 错误
问题描述
有以下查询模型:
return \DB::table('clients')->select(DB::raw("
DATE_FORMAT(result_test.created_at, '%M %e %Y') AS day,
COUNT(result_test.id) AS passed"))
->leftJoin('result_test', 'result_test.client_id', '=', 'clients.id')
->where('clients.doctor_id', $user_id)
->whereBetween('result_test.created_at', array($startDate, $endDate))
->orderBy('result_test.created_at')
->groupBy('day')
->get();
请注意:
DB::raw("
DATE_FORMAT(result_test.created_at, '%M %e %Y') AS day,
COUNT(result_test.id) AS passed")
之后,我尝试按以下方式对行进行分组AS day
:
->groupBy('day')
我收到了这个错误信息:
42000/1055 - db.result_test.created_at' isn't in GROUP BY
查询有什么问题?
当我删除了这个:->orderBy('result_test.created_at')
它就可以工作了。为什么?
解决方案
你得到这个是因为'result_test.created_at'
不在一个选择组中。
return \DB::table('clients')->select('result_test.created_at',DB::raw("
DATE_FORMAT(result_test.created_at, '%M %e %Y') AS day,
COUNT(result_test.id) AS passed"))
->leftJoin('result_test', 'result_test.client_id', '=', 'clients.id')
->where('clients.doctor_id', $user_id)
->whereBetween('result_test.created_at', array($startDate, $endDate))
->orderBy('result_test.created_at')
->groupBy('day')
->get();
希望这可以帮助。