假设业务表是A,面积字段:mj,时间字段:gmt_create,要统计面积字段。
1. 统计近半年(近6个月)的数据
- 日期表sql:有点像临时表
select tmp.year_month from (
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month`
) tmp
- 结合业务实现的sql实现如下
SELECT
tmp.months,
IF (isnull(bs.mj),0,bs.mj) AS mj
FROM
(
SELECT
DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS months UNION
SELECT
DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT
DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT
DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT
DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `year_month` UNION
SELECT
DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `year_month`
) tmp
LEFT JOIN (
-- 按时间分组且统计数据
SELECT IF (ISNULL(sum(a.mj)),0,sum(a.mj)) AS mj,DATE_FORMAT(a.gmt_create,'%Y-%m') AS months FROM a GROUP BY mj,months
) AS bs ON tmp.months = bs.months
WHERE
1 =1 GROUP BY months,mj
2.统计近一年每个月份的数据
- 日期表的sql
select tmp.year_month from (
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `year_month`
) tmp
- 结合业务表的sql,公式大致如下:
select 日期表.time, 业务表mj
from 日期表
left join 业务表 on 日期表.time = 业务表.time
上面的业务表是已经把主要的统计逻辑完成的,即统计数据且按时间分过组的。日期表只是为了补全某个日期内没有数据会跳过统计的问题。例如,本来01、02、03、04、05、06这几个月份,但04和05没有数据,则查出来的是01、02、03、06。
3.下个月
SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m');
4、近几年的年份
SELECT
year(DATE_SUB( now(), INTERVAL 6 YEAR )) AS tmp_date UNION ALL
SELECT
year(DATE_SUB( now(), INTERVAL 5 YEAR )) AS tmp_date UNION ALL
SELECT
year(DATE_SUB( now(), INTERVAL 4 YEAR )) AS tmp_date UNION ALL
SELECT
year(DATE_SUB( now(), INTERVAL 3 YEAR )) AS tmp_date UNION ALL
SELECT
year(DATE_SUB( now(), INTERVAL 2 YEAR )) AS tmp_date UNION ALL
SELECT
year(DATE_SUB( now(), INTERVAL 1 YEAR )) AS tmp_date UNION ALL
SELECT
year(DATE_SUB( now(), INTERVAL 0 YEAR )) AS tmp_date
案例
SELECT a.tmp_date,IFNULL(tmp.count,0) AS countFROM (
SELECT YEAR (DATE_SUB(now(),INTERVAL 5 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 4 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 3 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 2 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 1 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 0 YEAR)) AS tmp_date) a LEFT JOIN (
SELECT YEAR,sum(count) countFROM (
SELECT 'pd' type,count(*) count,DATE_FORMAT(create_time,'%Y') YEAR FROM bs_pdda GROUP BY DATE_FORMAT(create_time,'%Y') UNION ALL
SELECT 'zd' type,count(*) count,DATE_FORMAT(create_time,'%Y') YEAR FROM bs_zdda GROUP BY DATE_FORMAT(create_time,'%Y') UNION ALL
SELECT 'gd' type,count(*) count,DATE_FORMAT(create_time,'%Y') YEAR FROM bs_gdda GROUP BY DATE_FORMAT(create_time,'%Y')) AS a GROUP BY YEAR) AS tmp ON a.tmp_date=tmp.YEAR
近六年每年的数据