最近写的一个Mysql存储过程
停了半天的电,看了半天的<老子>,然后......
(思绪飞弛.....)
发现像排行榜更新之类的应用,还是用存储过程舒服,一个叫uch_zcount的表里有zcid,total,heronum 三个字段,现在要求按total从大到小把排行次序写到heronum字段中,zcid为自增字段........写了如下的存储过程:
DELIMITER $$;
DROP PROCEDURE IF EXISTS `zyf`.`getcount`$$
CREATE DEFINER=`root`@`%` PROCEDURE `getcount`()
BEGIN
set @ok=1;
set @i =1;
set @zcid=0;
select count(*) into @ok from uch_zcount;
CREATE TEMPORARY TABLE tmp_tablezz (
rowid bigint auto_increment primary key,
zcbigint);
insert into tmp_tablezz (zcid) select zcid from uch_zcount order by total desc;
while @i <@ok+1 do
update uch_zcount set heronum = @i where zcid= (select zcid from tmp_tablezz where rowid =@i);
set @i=@i+1;
end while;
drop table tmp_tablezz;
END$$
DELIMITER ;$$
DROP PROCEDURE IF EXISTS `zyf`.`getcount`$$
CREATE DEFINER=`root`@`%` PROCEDURE `getcount`()
BEGIN
set @ok=1;
set @i =1;
set @zcid=0;
select count(*) into @ok from uch_zcount;
CREATE TEMPORARY TABLE tmp_tablezz (
rowid bigint auto_increment primary key,
zcbigint);
insert into tmp_tablezz (zcid) select zcid from uch_zcount order by total desc;
while @i <@ok+1 do
update uch_zcount set heronum = @i where zcid= (select zcid from tmp_tablezz where rowid =@i);
set @i=@i+1;
end while;
drop table tmp_tablezz;
END$$
DELIMITER ;$$
肯定有更好的方法......... MYSQL还是不熟地...