Laravel条件内计数

Question

我得到了"未知列'month'"。

$result = DB::table('project')
->select(array(
DB::raw('DATE_FORMAT(start_time,"%M") as month'),
DB::raw('count(case when DATE_FORMAT(finish_time,"%M") = month then 1 else null end) as finished')
))
->orderBy('start_time', 'ASC')
->groupBy('month')
->get();

Answer 1

您不能像在类似查询的同义词中定义的那样使用month-in-case。这样试试：

$result = DB::table('project')
->select(array(
DB::raw('DATE_FORMAT(start_time,"%M") as month'),
DB::raw('count(case when DATE_FORMAT(finish_time,"%M") = DATE_FORMAT(start_time,"%M") then 1 else null end) as finished')
))
->orderBy('start_time', 'ASC')
->groupBy('DATE_FORMAT(start_time,"%M")')
->get();

Answer 2

您还需要在groupby中使用DB:：Raw。只需检查下面的代码。

$result = DB::table('project')
->select(array(
DB::raw('DATE_FORMAT(start_time,"%M") as month'),
DB::raw('count(case when DATE_FORMAT(finish_time,"%M") = DATE_FORMAT(start_time,"%M") then 1 else null end) as finished')
))
->orderBy('start_time', 'ASC')
->groupBy(DB::Raw('DATE_FORMAT(start_time,"%M")')) // here you have to use DB::Raw()
->get();