php - 如何在数组中回显 GROUP BY mysql？

Question

我有这个数据

ID DATEREG     REQUEST
1  2016-10-24  PAPER
2  2016-10-24  PENCIL
3  2016-10-24  BALLPEN
4  2016-10-25  PAPER
5  2016-10-26  PENCIL

这是我用来计算总请求的查询。

    $rows = '';
    $query = "SELECT id,count(*) as total,date,request FROM list GROUP BY date,request ORDER BY id DESC LIMIT 10";
    $result = mysqli_query($con,$query);
    $total_rows =  $result->num_rows;
    if($result) 
    {
    $rowsv1 = mysqli_fetch_all($result, MYSQLI_ASSOC);
    print "<pre>";
    echo print_r($rowsv1);
    print "</pre>";
    }

以及请求仅显示1(DESC顺序)的示例结果:

[0] => Array
    (
        [id] => 5
        [total] => 3
        [datereg] => 2016-10-24
        [request] => PAPER
    )

我想看起来像这样:

[0] => Array
    (
        [id] => 5
        [total] => 3
        [datereg] => 2016-10-24
        [request1] => 1(total of PAPER)
        [request2] => 2(total of PENCIL)
        [request3] => 3(total of BALLPEN)
    )

谢谢

Answer 1

我对您的查询做了一些更改，所以现在首先按请求分组，然后按日期分组。但是您必须在 php 中聚合结果，以便将所有 3 个请求放入一个数组项中

   $query = "SELECT id,count(*) as total,date,request FROM list GROUP BY request, date ORDER BY id DESC LIMIT 10";

否则，它将按日期分组，这将导致将给定日期的结果聚合到仅一个请求。