车轮战项目 ，老板让我写个定时计算每日答题的总人数的存储过程，有点蒙逼，没写过存储过程
一步步来
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
