SQL按日期分组，但保留记录 - SQL Group By Date But Maintain Records

SQL Group By Date But Maintain Records

本文关键字：保留记录日期 SQL | 更新日期: 2023-09-27

我假设是php/cakephp/sql议会。我有一个History表，其中包含DATETIME.类型创建的列我将展示所有的历史记录，但按天排列。

------------------------------------------
id   created                what
------------------------------------------
1    2014-01-01 08:00:00   Someone ran
2    2014-01-01 09:00:00   Someone cried
2    2014-01-02 10:00:00   Someone spoke
2    2014-01-02 18:00:00   Someone worked

和我要找的结果

array(
'2014-01-01' => array(
                      0=>array('time'=>'8:00','what'=>'Someone ran'
                      1=>array('time'=>'9:00','what'=>'Someone cried'
                     ),
'2014-01-02' => array(
                      0=>array('time'=>'10:00','what'=>'Someone spoke',
                      1=>array('time'=>'18:00','what'=>'Someone worked',
                     )
)

我完全卡住了。我应该研究哪个SQL语句?

如果其他评论者同意，我将在cakephp中给出我的答案

 $histories = $this->History->find('all',
                    array(
                        'group'=>array('History.created')
            ));

 foreach ($histories as $item) {
                $key = date('Y-m-d',strtotime($item['History']['created']));
                if (!isset($groups[$key])) {
                    $groups[$key] = array(
                        'items' => array($item),
                        'count' => 1,
                    );
                } else {
                    $groups[$key]['items'][] = $item;
                    $groups[$key]['count'] += 1;
                }
            }