如何用雄辩的方式回报每个公司的总价值？ [英] How to return total value for each company using eloquent?

问题描述

所以我在数据库上有此表

so i have this table on database

+---------+------------+-------+
| Company |  periods   | value |
+---------+------------+-------+
| a1      | 01-01-2018 |  1000 |
| a1      | 01-02-2018 |   600 |
| a2      | 01-01-2018 |   500 |
| a2      | 01-03-2018 |   500 |
| a3      | 01-01-2018 |   500 |
| a3      | 01-02-2018 |   500 |
| a3      | 01-03-2018 |   500 |
+---------+------------+-------+

我希望得到的查询就像是如果我想要获取2018年1月2日的所有公司总价值，那么它将总结出每家公司直到01-02-2018

and what i expect to get on query is to be like if i want to get all company total value for 01-02-2018 then it will be summarize the value for each company until 01-02-2018

+---------+------------+-------+
| Company |  periods   | value |
+---------+------------+-------+
| a1      | 01-02-2018 |  1600 |
| a2      | 01-02-2018 |   500 |
| a3      | 01-02-2018 |  1000 |
+---------+------------+-------+ 

如果我想从01-03-2018获得总价值，那么它还将汇总每个公司的价值，直到01-03-2018

and if i want to get total value form 01-03-2018 then it also will summarize the value for each company until 01-03-2018

+---------+------------+-------+
| Company |  periods   | value |
+---------+------------+-------+
| a1      | 01-03-2018 |  1600 |
| a2      | 01-03-2018 |  1000 |
| a3      | 01-03-2018 |  1500 |
+---------+------------+-------+ 

如何雄辩地做到这一点？

how to do that on eloquent?

推荐答案

问题是像这样

当您按值列对行进行分组时，结果行中的期间值是多少？您知道在分组之前有几个不同的期间值。现在，在其中，MySQL应该在结果行中输入哪个期间值？？

when you group rows by 'value' column, in the result row what is gonna be the 'periods' value? you know there were several distinct 'period' values before you group. now among them, which 'period' value should MySQL put into your result row...?

所以' sql_mode = only_full_group_by 不允许这样做>'

so its not allowed by 'sql_mode=only_full_group_by'

您需要从选择查询中删除期间列，也可以汇总期间列。

either you need to remove 'periods' column from select query or you can aggregate the 'periods' column.

可能的解决方案。

$maxDate = // whatever date you wanna compare with.

$summery = Model::select(DB::row('Company, max(periods) AS periods, sum(value) AS value'))
    ->groupBy('Company')
    ->where('periods', '<=', $maxDate)
    ->get();

这篇关于如何用雄辩的方式回报每个公司的总价值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！