我需要帮助在laravel 5中设置这个查询。
这是我的表格示例…：
表名：mytable
user_id cost status created_at
--------------------------------------
1 10 Pending 2016-04-21 04:51:01
1 20 Pending 2016-04-22 04:51:01
1 100 Approved 2016-04-25 04:51:01
1 45 Pending 2016-04-28 04:51:01
1 60 Approved 2016-05-01 04:51:01
1 60 Approved 2016-05-01 04:51:01
1 160 Approved 2016-05-03 04:51:01
1 360 Pending 2016-05-05 04:51:01
1 260 Approved 2016-05-12 04:51:01
1 210 Pending 2016-05-21 04:51:01
基本上，我需要Laravel中的一个SQL语句，它将输出类似的内容或每个用户关闭，因为我可能有不同的用户ID
Totalpending Totalapproved TotalCost
April/2016 3 1 ( add cost for April)
May/2016 1 4 ( add all cost for May)
到目前为止，我已经有了这段代码，但是如何在一个查询中完成所有代码呢？
$query = DB::table('my_table')->select(DB::raw('MONTH(created_at) as m, YEAR(created_at) as y'), DB::raw('count(*) as pp'))->where('user_id' ,Auth::id())->where('status','Pending')
->groupBy('created_at')
->get();
var_dump($query);
但这个输出只是如下所示：。。。
array(1) {
[0]=>
object(stdClass)#321 (3) {
["m"]=>
string(1) "4"
["y"]=>
string(4) "2016"
["pp"]=>
string(1) "3"
}
}
或者我可以有这样的输出：其中"pp">待处理的总成本，"aa">已批准的总成本、"cc">总成本
array(1) {
[0]=>
object(stdClass)#321 (3) {
["m"]=>
string(1) "4"
["y"]=>
string(4) "2016"
["pp"]=>
string(1) "3"
["aa"]=>
string(1) "1"
["cc"]=>
string(1) "175"
}
}
如何显示其他批准的和总成本？？
如果您只使用SQL来实现这一点，那么下面的内容应该会对您有所帮助。
SELECT CONCAT_WS('/', MONTHNAME(`created_at`), YEAR(`created_at`)) AS `Datum`,
SUM(CASE WHEN `status` = 'Pending' THEN 1 ELSE 0 END) AS `TotalPending`,
SUM(CASE WHEN `status` = 'Approved' THEN 1 ELSE 0 END) AS `TotalApproved`,
SUM(`cost`) AS `TotalCost`
FROM `mytable`
GROUP BY `Datum`
ORDER BY `Datum` DESC;
但由于您使用的是Laravel的查询生成器，我想它可以像下面这样移植。
$query = DB::table('mytable')->select(
DB::raw(
'CONCAT_WS(''/'', MONTHNAME(created_at), YEAR(created_at)) as Datum,
SUM(CASE WHEN status = ''Pending'' THEN 1 ELSE 0 END) AS TotalPending,
SUM(CASE WHEN status = ''Approved'' THEN 1 ELSE 0 END) AS TotalApproved,
SUM(cost) as TotalCost'
)
)->where('user_id', Auth::id())->groupBy('Datum')->orderBy('Datum', 'desc')->get();
演示