MySQL按日期分组统计(按天统计,按月统计)
以下为您演示MySQL常用的日期分组统计方法:
按月统计(一)
SELECT
date_format( r.pay_time, '%Y-%m' ) pay_time,
r.pay_time,
SUM( r.actual_payment ) AS actual_payment,
r.org_id,
r.semester_year,
r.season,
count( * ) coun
FROM
scd_cost_charge_regester r
WHERE
r.`status` = '1'
AND r.semester_year = '2019'
AND r.season = '1'
AND r.pay_time > '2020-01-13 00:00:00'
AND r.pay_time < '2020-03-22 00:00:00'
AND r.org_id IN ( 299983449711776, 253959347479904 )
GROUP BY
date_format( r.pay_time, '%Y-%m' );
按天统计(二)
SELECT
date_format( r.pay_time, '%Y-%m-%d' ) pay_time,
r.pay_time,
SUM( r.actual_payment ) AS actual_payment,
r.org_id,
r.semester_year,
r.season,
count( * ) coun
FROM
scd_cost_charge_regester r
WHERE
r.`status` = '1'
AND r.semester_year = '2019'
AND r.season = '1'
AND r.pay_time > '2020-01-13 00:00:00'
AND r.pay_time < '2020-03-22 00:00:00'
AND r.org_id IN ( 299983449711776, 253959347479904 )
GROUP BY
date_format( r.pay_time, '%Y-%m-%d' );
按天统计(三)毫秒
SELECT
date_format( r.pay_time / 1000, '%Y-%m-%d' ) pay_time,
r.pay_time,
SUM( r.actual_payment ) AS actual_payment,
r.org_id,
r.semester_year,
r.season,
count( * ) coun
FROM
scd_cost_charge_regester r
WHERE
r.`status` = '1'
AND r.semester_year = '2019'
AND r.season = '1'
AND r.pay_time > '2020-01-13 00:00:00'
AND r.pay_time < '2020-03-22 00:00:00'
AND r.org_id IN ( 299983449711776, 253959347479904 )
GROUP BY
date_format( r.pay_time / 1000, '%Y-%m-%d' );
其他格式转换
SELECT
date_format( r.pay_time, '%Y-%m-%d %H:%i:%S' ) pay_time,
r.pay_time,
SUM( r.actual_payment ) AS actual_payment,
r.org_id,
r.semester_year,
r.season,
count( * ) coun
FROM
scd_cost_charge_regester r
WHERE
r.`status` = '1'
AND r.semester_year = '2019'
AND r.season = '1'
AND r.pay_time > '2020-01-13 00:00:00'
AND r.pay_time < '2020-03-22 00:00:00'
AND r.org_id IN ( 299983449711776, 253959347479904 )
GROUP BY
date_format( r.pay_time, '%Y-%m-%d %H:%i:%S' );
本文来自博客园,作者:格,转载请注明原文链接:https://www.cnblogs.com/yuangyuan/p/16449831.html