也借鉴了网上的做法,但不全相同,这里补全。
CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的数字,方便以后计算时间
CREATE TABLE `calendar ` (
`datelist` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 生成一个存储日期的表,datalist是字段名
INSERT INTO calendar (datelist) SELECT
adddate(
( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT("2016-12-01", '%Y-%m-%d') ----若写成这种格式,DATE_FORMAT("2016-12", '%Y-%m'),但时间不会累加,不知道为啥
),
INTERVAL numlist.id MONTH
) AS `date`
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
FROM
wx_num n1
CROSS JOIN wx_num AS n10
CROSS JOIN wx_num AS n100
CROSS JOIN wx_num AS n1000
CROSS JOIN wx_num AS n10000
) AS numlist;
SELECT cal.datelist , IF(buyTotal IS NULL , 0, buyTotal) buyTotal,IF(activateTotal IS NULL , 0, activateTotal) activateTotal
FROM
(
select
FROM_UNIXTIME(oi.pay_time, '%Y-%m') months,
count(case WHEN (oi.pay_status=1 and oi.order_status!=1) then 1 else NULL end ) buyTotal,
count(case WHEN (oi.syn_status=1 and oi.order_status!=1) then 1 else null end ) activateTotal
from order_info oi
where emp_no='cc0075'
group by months
) order_info
RIGHT JOIN calendar cal on order_info.months = cal.datelist
where calendar.datelist<='2017-03' ---这个时间可以写也可以不写