合作商需求:数据需要保存在EXCEL表格中:用户百度ID 游戏游戏角色名称 最后一次登陆时间 游戏内剩余金币金额,由于此合用商的注册用户有100多万个,也只能用存储过程来查询了,代码如下:
DROP procedure IF EXISTS Checksumlast;
DELIMITER $$
create procedure Checksumlast()
begin
declare _accName VARCHAR(1000);
declare _userName VARCHAR(1000);
declare stopFlag int default 0;
declare curuserName cursor
for select A.accName,U.userName from Account A,User U where A.accId=U.accId and A.partnerId=120019 and A.createDttm<='2010-07-23 23:59:59';
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag = 1;
open curuserName;
repeat
fetch curuserName into _accName,_userName;
if stopFlag = 0 then
if ( _accName is not null ) then
DELIMITER $$
create procedure Checksumlast()
begin
declare _accName VARCHAR(1000);
declare _userName VARCHAR(1000);
declare stopFlag int default 0;
declare curuserName cursor
for select A.accName,U.userName from Account A,User U where A.accId=U.accId and A.partnerId=120019 and A.createDttm<='2010-07-23 23:59:59';
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag = 1;
open curuserName;
repeat
fetch curuserName into _accName,_userName;
if stopFlag = 0 then
if ( _accName is not null ) then
select A.accName,U.userName,A.lastLoginDttm, U.cash from Account A,User U where A.accId=U.accId and U.userName=_userName and U.cash >0;
end if;
end if;
until stopFlag = 1
end repeat;
close curuserName;
END $$
DELIMITER ;
call Checksumlast();
end if;
end if;
until stopFlag = 1
end repeat;
close curuserName;
END $$
DELIMITER ;
call Checksumlast();
查询结果如下,有很多玩家的账号内剩余金币数量不多了,需要把几十万条空记录去掉。
接着用UltraEdit去掉Empty set(1.10 sec)及空白行,去除空白行的具体步骤:选择-替换%[ ^t]++^p即可,最后导入EXCEL中即可。
本文转自 trt2008 51CTO博客,原文链接:http://blog.51cto.com/chlotte/360247,如需转载请自行联系原作者