之前写的那个只能获取固定的某年，某季，某月，某天的统计，这让我做报表的很难受，于是上网百度加自己理解写了如下的方法

凡是结果同月的第二个方法便于一个报表统计，方法只需写一个，谨慎选择

月：

1.

SELECT
SUM( CASE MONTH ( a.createTime ) WHEN '1' THEN 1 ELSE 0 END ) AS january,
SUM( CASE MONTH ( a.createTime ) WHEN '2' THEN 1 ELSE 0 END ) AS february,
SUM( CASE MONTH ( a.createTime ) WHEN '3' THEN 1 ELSE 0 END ) AS march,
SUM( CASE MONTH ( a.createTime ) WHEN '4' THEN 1 ELSE 0 END ) AS april,
SUM( CASE MONTH ( a.createTime ) WHEN '5' THEN 1 ELSE 0 END ) AS may,
SUM( CASE MONTH ( a.createTime ) WHEN '6' THEN 1 ELSE 0 END ) AS june,
SUM( CASE MONTH ( a.createTime ) WHEN '7' THEN 1 ELSE 0 END ) AS july,
SUM( CASE MONTH ( a.createTime ) WHEN '8' THEN 1 ELSE 0 END ) AS august,
SUM( CASE MONTH ( a.createTime ) WHEN '9' THEN 1 ELSE 0 END ) AS september,
SUM( CASE MONTH ( a.createTime ) WHEN '10' THEN 1 ELSE 0 END ) AS october,
SUM( CASE MONTH ( a.createTime ) WHEN '11' THEN 1 ELSE 0 END ) AS november,
SUM( CASE MONTH ( a.createTime ) WHEN '12' THEN 1 ELSE 0 END ) AS december
FROM
`gm_buyerproduct` a
WHERE
YEAR(createTime) = YEAR("2018-9-6")

如果你就要确定的12个月的统计，用这个很清楚，但报表的横坐标就是固定值了，不好做其他的方法统计

2.

SELECT COUNT(createTime) countStatus,
MONTH(createTime) AS monthflg
FROM
`gm_buyerproduct`
WHERE
YEAR(createTime)=YEAR("2018-12-6")
GROUP BY monthflg

这方法好处是横坐标是变量，但是只能统计月份有数值，没数值不会显示，导致结构可能是：1月，2月，4月有值显示，3月没值就不显示

季：

1.

SELECT
SUM( CASE QUARTER ( a.createTime ) WHEN '1' THEN 1 ELSE 0 END ) AS aa,
SUM( CASE QUARTER ( a.createTime ) WHEN '2' THEN 1 ELSE 0 END ) AS ss,
SUM( CASE QUARTER ( a.createTime ) WHEN '3' THEN 1 ELSE 0 END ) AS dd,
SUM( CASE QUARTER ( a.createTime ) WHEN '4' THEN 1 ELSE 0 END ) AS ff

FROM
`gm_buyerproduct` a
WHERE
YEAR(createTime) = YEAR("2018-9-6")

效果如月1

2.

SELECT COUNT(createTime) createTime,
QUARTER(createTime) modifiedTime
FROM
`gm_buyerproduct`
WHERE
YEAR(createTime) = YEAR("2018-8-8")
GROUP BY modifiedTime

同月2

 

周：

SELECT COUNT(createTime) countStatus,
WEEK(createTime) weeks
FROM
`gm_buyerproduct`
WHERE
MONTH(createTime)=MONTH("2018-12-19")AND YEAR(createTime)=YEAR("2018-12-16")
GROUP BY weeks

结果同月2，我这是统计一个月的周，统计某年所有周将MONTH(createTime)=MONTH("2018-12-19")AND 去了就行

天：

SELECT DATE_FORMAT (createTime,"%m-%d") createTimes,COUNT(createTime) countStatus ,YEAR(createTime) years
FROM `gm_buyerproduct`
WHERE WEEK(createTime)=WEEK("2018-12-16") AND YEAR(createTime)=YEAR("2018-12-16")
GROUP BY createTimes

结果同月2这是统计某周的7天，根据需求来加减条件