php - 如何使sql查询按日期对结果进行分组和求和？

Question

在 yii2 应用程序中，我有 Payment 模型，其中包含字段 id(主键)、u_id(整数，表示制作此内容的人的 ID来自 User 模型的付款)，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  |
+--------------------------------+

问:我想按日期对结果进行分组，并汇总每天每个 u_id 的所有行的 sum 字段，并显示 总计行。怎么做...？也许没有 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          |
+-------------------------------------+

支付模型:

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>

Answer 1

试试这个方法:

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，因此我将其设置为每个 null 都变为 0，以便首先显示。