php 如何使用带有 sum() 列和 groupBy 的查询构建器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26003384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use query builder with sum() column and groupBy
提问by user3720435
How would I use query builder in Laravel to generate the following SQL statement:
我将如何在 Laravel 中使用查询构建器来生成以下 SQL 语句：
SELECT costType, sum(amountCost) AS amountCost
FROM `itemcosts`
WHERE itemid=2
GROUP BY costType
I have tried several things, but I can't get the sum()column to work with a rename.
我已经尝试了几件事，但我无法让该sum()列使用重命名。
My latest code:
我的最新代码：
$query = \DB::table('itemcosts');
$query->select(array('itemcosts.costType'));
$query->sum('itemcosts.amountCost');
$query->where('itemcosts.itemid', $id);
$query->groupBy('itemcosts.costType');
return $query->get();
回答by Jarek Tkaczyk
Using groupByand aggregate function (sum/ countetc) doesn't make sense.
使用groupBy和聚合函数（sum/count等）没有意义。
Query Builder's aggregates return single result, always.
查询生成器的聚合始终返回单个结果。
That said, you want rawselect for this:
也就是说，您要raw为此选择：
return \DB::table('itemcosts')
->selectRaw('costType, sum(amountCost) as sum')
->where('itemid', $id)
->groupBy('costType')
->lists('sum', 'costType');
Using listsinstead of getis more appropriate here, it will return array like this:
在这里使用lists而不是get更合适，它会像这样返回数组：
[
'costType1' => 'sumForCostType1',
'costType2' => 'sumForCostType2',
...
]
With getyou would have:
和get你在一起：
[
stdObject => {
$costType => 'type1',
$sum => 'value1'
},
...
]
