BEGIN
DECLARE b int default 0;
DECLARE roleName varchar(100);
DECLARE sociaty varchar(20);
DECLARE he int;
DECLARE rs CURSOR FOR SELECT sum( tb2.money + tb2.bMoney ) AS he, tb2 . * , tb1. * FROM role_information AS tb2 LEFT JOIN sociaty AS tb1 ON tb2.sociatyId = tb1.id GROUP BY tb2.id ORDER BY he DESC LIMIT 0 , 100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
truncate table money_list;
open rs;
FETCH rs INTO roleName, sociaty, he;
while b>1 do
insert into money_list(roleName,sociaty,money) values(roleName, sociaty, he);
FETCH rs INTO roleName, sociaty, he;/*取下一条记录*/
end while;
close rs;
/*insert into money_list (roleName,sociaty,money) values(11,11,22);*/END
DECLARE b int default 0;
DECLARE roleName varchar(100);
DECLARE sociaty varchar(20);
DECLARE he int;
DECLARE rs CURSOR FOR SELECT sum( tb2.money + tb2.bMoney ) AS he, tb2 . * , tb1. * FROM role_information AS tb2 LEFT JOIN sociaty AS tb1 ON tb2.sociatyId = tb1.id GROUP BY tb2.id ORDER BY he DESC LIMIT 0 , 100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
truncate table money_list;
open rs;
FETCH rs INTO roleName, sociaty, he;
while b>1 do
insert into money_list(roleName,sociaty,money) values(roleName, sociaty, he);
FETCH rs INTO roleName, sociaty, he;/*取下一条记录*/
end while;
close rs;
/*insert into money_list (roleName,sociaty,money) values(11,11,22);*/END
insert into money_list(roleName,sociaty,money)
select
SELECT tb1.roleName,tb1.sociaty,tb2.id,sum( tb2.money + tb2.bMoney ) AS he FROM role_information AS tb2 LEFT JOIN sociaty AS tb1 ON tb2.sociatyId = tb1.id
GROUP BY tb1.roleName,tb1.sociaty,tb2.id ORDER BY he DESC LIMIT 0 , 100;
GROUP BY tb1.roleName,tb1.sociaty,tb2.id ORDER BY he DESC LIMIT 0 , 100;
只是不知道结果如何
DECLARE b int default 0;
DECLARE froleName varchar(100);
DECLARE fsociaty varchar(20);
DECLARE fhe int;
DECLARE rs CURSOR FOR SELECT tb2.roleName,tb1.name,sum( tb2.money + tb2.bMoney ) AS he FROM role_information AS tb2 LEFT JOIN sociaty AS tb1 ON tb2.sociatyId = tb1.id
GROUP BY tb2.id ORDER BY he DESC LIMIT 0 , 100;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET b = 1;
truncate table money_list;
open rs;
FETCH rs INTO froleName, fsociaty, fhe;
while b<>1 do
insert into money_list(roleName,sociaty,money) values(froleName,fsociaty,fhe);
FETCH rs INTO froleName, fsociaty, fhe;/*取下一条记录*/
end while;
close rs;
/*insert into money_list (roleName,sociaty,money) values(11,11,22);*/END 这就是正确代码,晒晒
'截至2011-05-06 18:48:27 用户结帖率0.00% 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖