mysql - 如何从 DATETIME 表中按日期选择并按日期分组

Question

我有这张表:

CREATE TABLE `count_traffic` (
  `tool_id` int(11) NOT NULL,
  `leads` int(11) NOT NULL DEFAULT '0',
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

以及以下选择:

$query="
SELECT sum(`leads`) as leads
     , date  
  FROM count_traffic 
 WHERE tool_id = :tool_id  
   AND date BETWEEN '2018-03-13' AND '2018-03-14' 
 GROUP 
    BY date";

如果我使用 date 作为日期字段，此 SELECT 会显示良好的结果!如何在两个日期之间进行选择并仅从 datetime 字段类型中按日期分组？

Answer 1

您使用 DATE()函数并将您的 date 列包装在该函数调用中。

SELECT sum(`leads`) as leads
     , DATE(`date`)
  FROM count_traffic 
 WHERE tool_id = :tool_id  
   AND date BETWEEN '2018-03-13' AND '2018-03-14' 
 GROUP 
    BY DATE(`date`)