【sql】不同的时间字段进行汇总

Hi DaXiong

北京连着吹了几天的超级大的风

今天的风终于不再那么大了，

但今天还是把我头发吹出了小啾啾，

跟天线宝宝一样可爱

难怪我感觉今天的信号这么好，哈哈哈

SELECT 
  DATE(TimeStart) AS date, 
  COUNT(*) AS num
FROM track
WHERE Flag = 0 AND Duration >= 300 
GROUP BY date
ORDER BY date;

SELECT 
  DATE_FORMAT(TimeStart, '%Y-%m-%d %H:00:00') AS time, 
  COUNT(*) AS num
FROM track
WHERE Flag = 0 AND Duration >= 300
GROUP BY time
ORDER BY time;

SELECT 
  time, 
  COUNT( * ) AS num 
FROM
  (
  SELECT Duration,
    DATE_FORMAT(
      concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart )  10 ) * 10 ),
      '%Y-%m-%d %H:%i' 
    ) AS time 
  FROM tarck
  WHERE Flag = 0  AND Duration >= 300 
  ) a 
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY time;

SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:%i:00') AS time, COUNT(*) AS num
FROM track 
WHERE Flag = 0 AND Duration >= 300
GROUP BY time
ORDER BY time;