如何对sql查询进行分组并按日期求和?

In yii2 app I have Payment model with fields id (primary key), u_id (integer, means id of person that made this payment from User model), sum (integer) and date. Example:

在yii2应用程序中，我有带字段id(主键)、u_id (integer，表示从用户模型中付款的人的id)、sum (integer)和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: I want to group results by date and summarize sum fields of all rows for each u_id for every day and also show total row. How to do that...? Maybe without total row? On yii2 or clean mysql. Example of results:

Q:我想按日期对结果进行分组，总结每天每个u_id的所有行的和字段，并显示总行。怎么做……吗?也许没有全部行?使用yii2或clean 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 model:

支付模式:

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;
}

I guess ListView will be easer then GridView, because we will be able to make some calculations for each result. View:

我想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:

_item_view:

<tr>
    <td><?= $model->date ?></td>
    <td><?= $model->u_id ?></td>
    <td><?= $model->sum ?></td>
</tr>

3 个解决方案

#1

Try this way:

试试这种方法:

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)

See it here: http://sqlfiddle.com/#!9/48102/6

看到这里:http://sqlfiddle.com/ ! 9/48102/6

What you need in plain MySql would be possible with analytical functions but since MySql doesn't support it, you have to emulate it.

在普通的MySql中，你需要的是分析函数，但由于MySql不支持它，所以你必须模仿它。

In my solution, I've made a query that sum the money only by date

在我的解决方案中，我做了一个查询，该查询只按日期对钱进行总和

select dt, null as usr, sum(vsum) as money
  from mytable
 group by dt

The null as usr column is needed so I can use the UNION operator with the second part. This query will get all dates and money summed.

需要将null作为usr列，以便我可以使用UNION操作符来处理第二部分。这个查询将得到所有日期和资金的总和。

Then the second part

然后第二部分

select dt, u_id, sum(vsum) as money
  from mytable
 group by dt, u_id

Wich will get all dates by user summing the money.

用户把钱加起来就可以得到所有的约会。

The third part is to make it as subquery so I can order it with by date, user. Remember that the user of the first part is null, so I make it so every as null become 0 so it will be shown first.

第三部分是将它作为子查询，这样我就可以按日期、用户来排序。记住，第一部分的用户是null，所以我把它设为，所以每一个null都变成0，所以它首先会显示出来。

#2

You can use a SqlDataProvider this way

可以这样使用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,
    ]);

#3

One option is let mysql calculate the totals using WITH ROLLUP.

一种方法是让mysql使用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 |
+------------+------+-----------+

If you don't want the overall total, you can eliminate that as well.

如果你不想要总的总数，你也可以把它去掉。

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 |
+------------+------+-----------+

#1