MySql按年月日分组查询

心已赠人 2022-03-17 15:20 590阅读 0赞

### MySql日期的格式化 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NzM3Mjc0_size_16_color_FFFFFF_t_70][]

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()连接字符串
    
    -- month
    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_date, '%Y-%m') as aa,SUM(operation_balance) as money  from user_wallet_record where year(create_date) = 2019  group by month (create_date);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NzM3Mjc0_size_16_color_FFFFFF_t_70]: /images/20220317/4627056ec49d4242a53c8ef491d757c4.png