统计全年12个月数据

查询指定年份的count()值
这方法需要对没有数据的月份进行处理

SELECT YEAR(ordertime) year,
MONTH(ordertime) month,
SUM(1) num
FROM s_order
WHERE ordertime BETWEEN ‘2015-1-1’ AND ‘2015-12-31’
GROUP BY YEAR(ordertime),
MONTH(ordertime)

方法二:这方法需要定义一个1-12月份的实体类,所有月份全部显示,对应月份没有数据的列置零

SELECT COALESCE(COUNT(),0) AS Jan,
(SELECT IFNULL(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-02’)) AS Feb,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-03’)) AS Mar,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-04’)) AS Apr,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-05’)) AS May,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-06’)) AS Jun,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-07’)) AS Jul,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-08’)) AS Aug,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-09’)) AS Sept,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-10’)) AS Octo,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-11’)) AS Nov,
(SELECT COALESCE(COUNT(),0) FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-12’)) AS Dece
FROM s_order AS o
WHERE DATE_FORMAT(o.ordertime,’%Y-%m’) = CONCAT(#{0},’-01’)

查询结果为