mysql - 使用 PHP PDO 进行双重分组

Question

我正在尝试从 mySQL 数据库中获取记录并对它们进行分组。我们以此表为例:

+++++++++++++++++++++++++++++++++++++++++++++++++++++
DATEADDED            DOCTYPE     SUBJECT    DETAILS
2018-03-03 9:54:54   DOCUMENT     Letter     Outgoing
2018-03-03 8:54:54   PARCEL       Mail       Incoming
2018-03-04 8:55:54   PARCEL       Mail       Incoming
2018-03-04 8:55:54   DOCUMENT     Mail       Outgoing
+++++++++++++++++++++++++++++++++++++++++++++++++++++

我想做的是按日期(不包括时间)对这些数据进行分组，然后按类型和计数进行分组，这样它看起来像这样:

+++++++++++++++++++
DATE: April 3, 2018
      DOCUMENT - 1
      PARCEL -   1
 TOTAL-----------2

DATE: April 4, 2018
      DOCUMENT - 1
      PARCEL -   1
 TOTAL-----------2                     
++++++++++++++++++++

已尝试使用以下代码:

<?php 
foreach($page->query('SELECT id,dateAdded,COUNT(*) FROM seclogs GROUP BY DATE(dateAdded) desc') as $row) { ?><tr>
<td><?php echo date("Y-m-d", strtotime($row['dateAdded'])); ?></td>
<td><?php 

$thisDate=date(strtotime($row['dateAdded']));
foreach($page->query("SELECT id,dateAdded,docType,COUNT(*) FROM seclogs WHERE DATE(dateAdded) LIKE '$thisDate' GROUP BY docType desc") as $row2) {

echo $row2['docType']."-".$row2['COUNT(*)']."<br/>"; } ?><br/><b><?php echo "Total: ".$row['COUNT(*)'].""; ?></b> </td> 
</tr><?php } ?>

但它只显示以下输出:

+++++++++++++++++++
DATE: April 3, 2018


 TOTAL-----------2

DATE: April 4, 2018


 TOTAL-----------2                     
++++++++++++++++++++

我一直在到处寻找这个问题，但找不到任何答案。任何帮助将不胜感激。

Answer 1

您想要的确切输出实际上是一个演示文稿，并且可以在 PHP 代码中相当轻松地处理。至于查询，以下是我将用于您需要的原始数据的内容:

SELECT
    DATE(dateAdded) AS dateAdded,
    docType,
    COUNT(*) AS cnt
FROM seclogs
GROUP BY
    DATE(dateAdded),
    docType
ORDER BY
    DATE(dateAdded),
    docType;

当然，这不会给出您想要的输出，至少不完全是。您现在需要做的是使用一个循环，仅打印每个日期/类型组开头的日期。