mysql 人大金仓 按照日 周 月 年 分组查询

'%Y-%m-%d %H:%i:%s'

 

查询每天的数据

myslq：
SELECT COUNT(1) AS total, DATE_FORMAT(created_time,'%Y-%m-%d') AS days FROM table_test GROUP BY days ;

金仓：
SELECT COUNT(1) AS total,to_char(created_time,'%Y-%m-%d') AS days FROM table_test GROUP BY days ;

查询每周的数据

mysql:
SELECT COUNT(1) AS total, WEEK(created_time) AS weeks FROM table_test GROUP BY weeks ;

金仓：
SELECT COUNT(1) AS total, WEEK(created_time) AS weeks FROM table_test GROUP BY weeks ;

查询每月的数据

mysql:
SELECT COUNT(1) AS total, MONTH(created_time) AS months FROM table_test GROUP BY months ;
SELECT COUNT(1) AS total, DATE_FORMAT(created_time, '%Y-%M') AS months FROM table_test GROUP BY months ;

金仓：
SELECT COUNT(1) AS total, to_char(created_time, 'yyyy-MM') AS months FROM table_test GROUP BY to_char(created_time, 'yyyy-MM') ;
SELECT COUNT(1) AS total, to_char(created_time, '%Y-%M') AS months FROM table_test GROUP BY months ;
SELECT COUNT(1) AS total, MONTH(created_time) AS months FROM table_test GROUP BY months ;

 查询每年的数据

mysql:
SELECT COUNT(1) AS total, YEAR(created_time) AS years FROM table_test GROUP BY years ;
SELECT COUNT(1) AS total, DATE_FORMAT(created_time,'%Y') AS years FROM table_test GROUP BY years ;

金仓：
select COUNT(1) AS total, to_char(created_time, 'yyyy') AS year FROM table_test GROUP BY to_char(created_time, 'yyyy');
select COUNT(1) AS total, to_char(created_time, '%Y') AS year FROM table_test GROUP BY year;

 

注意mysql 有如下函数：
DATE_ADD(createTime,INTERVAL 1 DAY) 得到指定日期后一天的日期/把1改为任意数字就可以得到后N天的日期
DATE_SUB(createTime,INTERVAL 1 DAY) 得到指定日期前一天的日期/把1改为任意数字就可以得到前N天的日期