select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;
1、按照月份：
select sum(total_amount) as total, date_format(stat_date, '%Y-%m') from week_report WHERE `stat_date` BETWEEN '2016-11-02' AND '2017-04-30' group by date_format(stat_date, '%Y-%m');
select sum(total_amount) as total,date_format(stat_date, '%Y-%m') from week_report WHERE `stat_date` BETWEEN '2016-12-11' AND '2016-12-22' group by date_format(stat_date, '%Y-%m');
获得按照月份分组进行汇总的数据。
concat()连接字符串
select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%m')) months ,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx
WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by months;
select CONCAT(YEAR(stat_date),'_',quarter(stat_date)) qu,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx
WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by qu;
select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%U')) weeks,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx
WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by weeks;
select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%m'),'_',DATE_FORMAT(stat_date,'%d')) days, sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx
WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by days;
select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks; select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days; select DAT...
假设表
中
有一个字段time,格式为Unix时间戳，现需要按照该字段统计每天的记录数。
利用convert函数convert(char(10),time,120) as time，然后group by time即可。
原理：convert函数将时间戳转换成ODBC标准时间(120参数指定)，然后取前十位，则刚好得到完整日期(不含时分秒)，再
分组
即可。
按月，按年
分组
方法一样，只需要修改convert()方法第一个参数的长度。
您可能感兴趣的文章:
分组
后
分组
合计以及总计SQL语句（稍微整理了一下）SQL SERVER
分组
求和sql语句MY
subpartition by hash(quarter(date2 ))
subpartitions 4 (
partition p0 values less than (2015),
partition p2015 values less than (2016),
在
MySQL
中
，可以使用 GROUP BY 子句来实现
分组
查询。如果需要多条件
分组
，可以在 GROUP BY 子句
中
指定多个字段。例如，假设有一个学生表 student，包含字段 name、age 和 gender，我们可以按照年龄和性别进行
分组
查询：
```sql
SELECT age, gender, COUNT(*) as count
FROM student
GROUP BY age, gender;
上述查询会按照 age 和 gender 两个字段进行
分组
，并计算每个
分组
中
的记录数。查询结果将会显示每个
分组
的年龄、性别和记录数。
