第一个存储过程

车轮战项目 ，老板让我写个定时计算每日答题的总人数的存储过程，有点蒙逼，没写过存储过程

一步步来

1.首先先写一个SQL 查询当天答题总人数（非手机端）

select create_time,season_code,count(distinct user_id)

from wheel_war.epg_answer_record

where DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d') and length(user_id) not in (11);

这里用到了：

（1）获取系统当前时间并且格式成我们需要的格式DATE_FORMAT(now(),'%Y-%m-%d') 

（2）非手机端加了个判断length(user_id) not in (11)；长度不等于11

2.查询出结果后我需要插入到另一张表

insert into wheel_war_count.wheel_user_stat (create_time,name,code,user_num) values (now(),'IPTV真实答题人数',code,iptvCount);

原本是想插同一张库的，但是要求插入另个库，于是插入前面加了库名.表名

3.我需要几个变量用户来存放查询结果 为手机人数、电视人数、时间

存储过程中声明变量为

declare boboCount int;

declare iptvCount int;

declare code varchar(255);

declare times datetime;

4.声明变量后需要将查询的数据插入到变量中

select create_time,season_code,count(distinct user_id) into times,code,iptvCount

from wheel_war.epg_answer_record

where DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d') and length(user_id) not in (11);

5.最终成型了存储过程

BEGIN

declare boboCount int;

declare iptvCount int;

declare code varchar(255);

declare times datetime;

select create_time,season_code,count(distinct user_id) into times,code,iptvCount

from wheel_war.epg_answer_record

where DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d') and length(user_id) not in (11);

insert into wheel_war_count.wheel_user_stat (create_time,name,code,user_num) values (now(),'IPTV真实答题人数',code,iptvCount);

end

然后根据需求增加手机端的部分

BEGIN

declare boboCount int;

declare iptvCount int;

declare code varchar(255);

declare times datetime;

select create_time,season_code,count(distinct user_id) into times,code,boboCount

from wheel_war.epg_answer_record

where DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d') and length(user_id) in (11);

select create_time,season_code,count(distinct user_id) into times,code,iptvCount

from wheel_war.epg_answer_record

where DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d') and length(user_id) not in (11);

insert into wheel_war_count.wheel_user_stat (create_time,name,code,user_num) values (now(),'手机端真实答题人数',code,boboCount);

insert into wheel_war_count.wheel_user_stat (create_time,name,code,user_num) values (now(),'IPTV真实答题人数',code,iptvCount);

end