#第一步:创建存储过程

DROP PROCEDURE IF EXISTS everyCreateCount;

DELIMITER $$

CREATE PROCEDURE everyCreateCount()

BEGIN

DECLARE mintime VARCHAR(10) DEFAULT (SELECT DATE(createdate) FROM `tb_user` ORDER BY createdate ASC LIMIT 1);

DECLARE days INT DEFAULT DATEDIFF(CURDATE(),mintime);

SET @d = -1;

SELECT createdate, SUM(total) total FROM(

SELECT @d:=@d+1 d,ADDDATE(mintime,@d) createdate,(SELECT 0) total FROM customer_info WHERE @d<days

UNION ALL

SELECT (SELECT 0) d,DATE(createdate) createdate,COUNT(`userid`) total FROM `tb_user` GROUP BY DATE(createdate)

ORDER BY createdate ASC

) temp2 GROUP BY createdate

#条件添加处,1=1 后面用and添加搜索条件，当然temp2临时表结果要包含该条件

HAVING 1=1 ;

END;

$$

DELIMITER ;

CALL everyCreateCount();

 

DROP PROCEDURE IF EXISTS payInfoCreateCount;

DELIMITER $$

CREATE PROCEDURE payInfoCreateCount(IN method VARCHAR(20))

BEGIN

DECLARE mintime VARCHAR(10) DEFAULT (SELECT DATE(payDate) FROM `tb_pay_info` ORDER BY payDate ASC LIMIT 1);

DECLARE days INT DEFAULT DATEDIFF(CURDATE(),mintime);

SET @d = -1;

SELECT payDate AS createTime, SUM(total) AS total,GROUP_CONCAT(paymentmethod) paymentmethod FROM(

SELECT @d:=@d+1 d,ADDDATE(mintime,@d) payDate,(SELECT 0) total,(SELECT NULL) paymentmethod FROM customer_info WHERE @d<days

UNION ALL

SELECT (SELECT 0) d,DATE(payDate) payDate,COUNT(`orderId`) total,paymentmethod FROM `tb_pay_info` WHERE paymentmethod=method GROUP BY DATE(payDate)

ORDER BY payDate ASC

) temp2 GROUP BY payDate

#条件添加处,1=1 后面用and添加搜索条件，当然temp2临时表结果要包含该条件

HAVING 1=1 AND temp2.payDate>'2019-01-28';

END;

$$

DELIMITER ;

CALL payInfoCreateCount('directPay');

xczfPay

dgzhPay

weixinSmPcPay

directPay

SELECT `payState`,`payDate`,`paymentmethod` FROM `tb_pay_info` WHERE`payState`='FINISH' AND payDate>'2018-08-07' ;

SET @mintime=(SELECT DATE(payDate) FROM `tb_pay_info` ORDER BY payDate ASC LIMIT 1);

SET @days = (DATEDIFF(CURDATE(),@mintime));

SELECT @mintime,@days ;

SET @d = -1;

SELECT @d:=@d+1 d,ADDDATE(@mintime,@d) payDate,(SELECT 0) total FROM customer_info WHERE @d<@days

 

 

 

 

 

 

 

 

 

 

-- 定义自增变量

set @mintime=(SELECT DATE(createdate) FROM `tb_user` ORDER BY createdate asc LIMIT 1);

SET @days = DATEDIFF(CURDATE(),@mintime);

SET @d = -1;

select createdate, sum(total) total from(

SELECT @d:=@d+1 d,ADDDATE(@mintime,@d) createdate,(SELECT 0) total FROM customer_info WHERE @d<@days

union all

SELECT (select 0) d,DATE(createdate) createdate,COUNT(`userid`) total FROM `tb_user` GROUP BY DATE(createdate)

order by createdate asc

) temp2 GROUP BY createdate

#条件添加处

having temp2.createdate>'2017-06-04';

/**

比如这个 上面的createWay 的pc和wx两个条件 再根据pc和wx分别查下面的类型

根据createway查 类型是type 的数据

:

我觉着一张图做不出来....分成两张图还好些 一个pc 一个wx

我:

一个是用户，一个是订单，具体要统计什么？

:

都是单表的 根据日期统计order_info里面 createway 的pc端办理的证书新办 续期 解锁 注销 日期 而且能根据日期查出来的那种

:

和微信wx端办理的证书新办 续期 解锁 注销 日期 而且能根据日期查出来的那种

*/

 

 

 

 

 

 

 

#第一步:创建存储过程

DROP PROCEDURE IF EXISTS everyCreateCount;

DELIMITER $$

CREATE PROCEDURE everyCreateCount()

BEGIN

DECLARE minDate VARCHAR(10) DEFAULT (SELECT DATE(createdate) FROM `tb_user` ORDER BY createdate ASC LIMIT 1);

DROP TABLE IF EXISTS statisticHelp;

CREATE TABLE statisticHelp (dd VARCHAR(10));

WHILE minDate<=CURDATE() DO

INSERT INTO statisticHelp VALUES(minDate);

SET minDate=DATE_ADD(minDate,INTERVAL 1 DAY);

END WHILE ;

END;

$$

DELIMITER ;

#第二步:执行,给中间表添加初始数据

CALL everyCreateCount();

#第三步:每天定时更新中间表中的数据

SET GLOBAL event_scheduler = ON;

DROP EVENT IF EXISTS `event1`;

DELIMITER ;;

CREATE EVENT `event1` ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(),INTERVAL 2 HOUR),INTERVAL 30 MINUTE) ON COMPLETION PRESERVE ENABLE COMMENT '每天凌晨2:30执行一次'

DO

BEGIN

call everyCreateCount();

END

;;

DELIMITER ;

#第四步：最终结果--XML中要写的

SELECT DATE(createdate )createdate, SUM(total) total FROM (

SELECT DATE(createdate) createdate,COUNT(`userid`) total FROM `tb_user` GROUP BY DATE(createdate)

UNION ALL

SELECT DATE(`dd`) createdate,(SELECT 0) total FROM `statisticHelp`

) temp GROUP BY createdate ORDER BY createdate ASC;