bitscn.com
mysql函数实例-统计日新增用户
sql代码 create function `statics_user_new`() returns int(11) comment '统计新增用户' begin #routine body goes here... declare stopflag int default 0 ; declare _shop_id varchar(11) default null; #餐厅id declare _device varchar(50) default null; #手机设备号 declare _a_token char(64) default null; #与苹果服务器会话 declare _s_token varchar(64) default null; #与点菜网服务器会话 declare _counts int default 0; #查询昨天新创建的用户 declare cur1 cursor for select id, device, a_token, s_token from visitor_user where date_format(create_time, '%y-%m-%d') = date_sub(date_format(now(),'%y-%m-%d'), interval 1 day); declare continue handler for not found set stopflag=1; open cur1; fetch cur1 into _shop_id, _device, _a_token, _s_token; while stopflag = 0 do insert into report_user_new_day_detail(id, shop_id, device, a_token, s_token) values (uuid(), _shop_id, _device, _a_token, _s_token); fetch cur1 into _shop_id, _device, _a_token, _s_token; end while; close cur1; #统计日新增用户数 select count(device) into _counts from visitor_user where date_format(create_time, '%y-%m-%d') = date_sub(date_format(now(),'%y-%m-%d'), interval 1 day); insert into report_user_new_day(id, day_time, new_counts, type_client) values(uuid(), date_sub(date_format(now(),'%y-%m-%d'), interval 1 day), _counts, 0); set _counts = 1; return _counts; end
bitscn.com