在yii2应用程序中,我有Payment
模型,字段为id
(主键)、u_id
(整数,表示从User
模型付款的人的id)、sum
(整数)和date
。示例:
+--------------------------------+
| Payment |
+--------------------------------+
| id | u_id | sum | date |
+--------------------------------+
| 1 | 1 | 400 | 2015-11-25 |
| 2 | 1 | 200 | 2015-11-25 |
| 3 | 2 | 500 | 2015-11-25 |
| 4 | 2 | 300 | 2015-11-25 |
| 5 | 1 | 100 | 2015-11-20 |
+--------------------------------+
Q:我想按日期对结果进行分组,并为每天的每个u_id
汇总所有行的sum
字段,还显示total
行。如何做到。。。?也许没有total
行?在yii2或清理mysql上。结果示例:
+-------------------------------------+
| Date | User id | Money, $ |
+-------------------------------------+
| 2015-11-25 | | 1400 (total) |
| 2015-11-25 | 1 | 600 |
| 2015-11-25 | 2 | 800 |
| 2015-11-20 | | 100 (total) |
| 2015-11-20 | 1 | 100 |
+-------------------------------------+
Payment
型号:
public function search($params)
{
$query = Payment::find();
// do we need to group and sum here?
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
$this->load($params);
return $dataProvider;
}
我想ListView
会比GridView
更容易,因为我们可以对每个结果进行一些计算。视图:
<table>
<thead>
<tr>
<th>Date</th>
<th>User id</th>
<th>Money, $</th>
</tr>
</thead>
<tbody>
<?= ListView::widget([
'dataProvider' => $dataProvider,
'itemView' => '_item_view',
]) ?>
</tbody>
</table>
_item_view
:
<tr>
<td><?= $model->date ?></td>
<td><?= $model->u_id ?></td>
<td><?= $model->sum ?></td>
</tr>
试试这个方法:
select dt "Date", usr "User Id",
case when usr is null
then concat(money, ' (total)')
else money
end as "Money, $"
from (
select dt, null as usr, sum(vsum) as money
from mytable
group by dt
union
select dt, u_id, sum(vsum) as money
from mytable
group by dt, u_id
) a
order by dt desc, coalesce(usr,0)
请在此处查看:http://sqlfiddle.com/#!9/48102/6
在普通MySql中,分析函数可以满足您的需求,但由于MySql不支持它,您必须模拟它。
在我的解决方案中,我做了一个查询,只按日期计算金额
select dt, null as usr, sum(vsum) as money
from mytable
group by dt
需要null as usr
列,这样我就可以在第二部分中使用UNION
运算符。此查询将获得所有日期和金额的总和。
然后第二部分
select dt, u_id, sum(vsum) as money
from mytable
group by dt, u_id
Wich将通过用户将钱相加来获得所有日期。
第三部分是将其作为子查询,这样我就可以按日期、用户订购。请记住,第一部分的用户是null,所以我将其设为每个asnull变为0
,这样它将首先显示。
您可以通过这种方式使用SqlDataProvider
$count = Yii::$app->db->createCommand('
SELECT COUNT(*) FROM payment group by `date`, u_id', )->queryScalar();
$dataProvider = new SqlDataProvider([
'sql' => 'SELECT u_id, sum(`sum`) as `sum` , `date` FROM payment group by `date`, u_id',
'totalCount' => $count,
'sort' => [
'attributes' => [
'u_id',
'sum',
'date',
],
],
'pagination' => [
'pageSize' => 20,
],
]);
return $this->render('index', [
'dataProvider' => $dataProvider,
]);
一个选项是让mysql使用WITH ROLLUP
计算总数。
select dt, u_id, sum(vsum)
from payment
group by dt, u_id with rollup;
+------------+------+-----------+
| dt | u_id | sum(vsum) |
+------------+------+-----------+
| 2015-11-20 | 1 | 100 |
| 2015-11-20 | NULL | 100 |
| 2015-11-25 | 1 | 600 |
| 2015-11-25 | 2 | 800 |
| 2015-11-25 | NULL | 1400 |
| NULL | NULL | 1500 |
+------------+------+-----------+
如果你不想要总的总数,你也可以去掉它。
select * from (
select dt, u_id, sum(vsum)
from payment
group by dt, u_id with rollup
) q where dt is not null;
+------------+------+-----------+
| dt | u_id | sum(vsum) |
+------------+------+-----------+
| 2015-11-20 | 1 | 100 |
| 2015-11-20 | NULL | 100 |
| 2015-11-25 | 1 | 600 |
| 2015-11-25 | 2 | 800 |
| 2015-11-25 | NULL | 1400 |
+------------+------+-----------+