本文共 2112 字，大约阅读时间需要 7 分钟。

CREATE DEFINER=`lifunet`@`%` PROCEDURE `proc_rank_article`( in inter int ) begin declare temp_reply_article_id int; declare temp_reply_total int default 0; declare temp_article_good int default 0; declare temp_article_sort int default 0; declare done boolean; -- declare tmp_article_cur cursor for select reply_article_id from tmp_article_table; -- declare continue handler for not found set done=true; drop temporary table if exists tmp_article_table; drop temporary table if exists tmp_article_static_table; create temporary table tmp_article_table(reply_article_id int); create temporary table tmp_article_static_table(article_id int,good_click int,replyTotal int,article_sort int); if(inter is not null) then if(inter = 0) then set inter = 7; end if; if(inter = 1) then set inter = 360; end if; if(inter = 2) then set inter = 30; end if; insert into tmp_article_table(reply_article_id) select reply_article_id from t_article where reply_article_id is not null and create_time > DATE_ADD(now(), INTERVAL -inter DAY) order by create_time desc limit 10000; -- start transaction; open tmp_article_cur; tmp_article_cur_loop:loop fetch tmp_article_cur into temp_reply_article_id; if done then leave tmp_article_cur_loop; else if(NOT EXISTS (select article_id from tmp_article_static_table where article_id = temp_reply_article_id )) then -- select count(good_click) into temp_article_good from t_record where article_id=temp_reply_article_id and good_click is not null and create_time > DATE_ADD(now(), INTERVAL -inter DAY); -- select count(reply_article_id) into temp_reply_total from tmp_article_table where reply_article_id=temp_reply_article_id; -- X= set temp_article_sort = temp_article_good + temp_reply_total; insert into tmp_article_static_table (article_id,good_click,replyTotal,article_sort) values(temp_reply_article_id,temp_article_good,temp_reply_total,temp_article_sort); end if; end if; end loop tmp_article_cur_loop; close tmp_article_cur; -- select * from tmp_article_static_table order by article_sort desc; drop table tmp_article_table; drop table tmp_article_static_table; commit; end if; end;

转载地址：http://vtebb.baihongyu.com/