Laravel Eloquent，按月/年分组 [英] Laravel Eloquent, group by month/year

问题描述

我正在尝试按月和年对数据进行分组.

i'm trying to group my data by month and year.

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
           ->groupby('year','month')
           ->get();

输出为:

{
"data": 19215,
"year": 2016,
"month": 10
},

如果我仅按月分组，我不知道本月是哪一年，那么我的预期输出是:

if i group only by month, i don't know from which year belong this month, my expected output is :

{
"clicks": 19215,
"month": 11-2016,
},
{
"clicks": 11215,
"month": 12-2016,

}，

我想用sql而不是php来做.

i want to do it in sql, not in php.

推荐答案

您可以尝试以下方式:

->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();

这篇关于Laravel Eloquent，按月/年分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！