mysql存储过程 送分了。
码工码工 2009-05-20 04:26:48 统计需求:有多少本(完本)图书被完全阅读了?
mylogs表里放的是每天的阅读日志(分析Apache日志所得),book表里放的是图书的信息,比如某本书有多少章,作者,书名等等。
写了如下的存储过程,
create procedure wanbens(in d int, in m int ,in y int)
begin
declare total_nums int default 0;
declare mxindexed int default 0;
declare mxindex int default 0;
declare bid varchar(20) default '';
declare tmp_bid int default 0;
declare i int default 0;
declare wbbook_nums int default 0;
select count( distinct para_bookid ) into total_nums from mylogs where log_day=d and log_month=m and log_year=y;
while i<total_nums do
select para_bookid into bid from mylogs where para_bookid<>tmp_bid and log_day=d and log_month=m and log_year=y order by para_bookid limit 1;
if bid <> tmp_bid then
set tmp_bid = 0;
select para_bookid, para_index into tmp_bid, mxindexed from mylogs where para_bookid=bid and log_day=d and log_month=m and log_year=y order by para_index desc limit 1;
select max_chapter into mxindex from book where bookid=tmp_bid limit 1;
if mxindexed = mxindex then
set wbbook_nums = wbbook_nums+1;
end if;
end if;
set i = i+1;
set bid = 0;
end while;
select wbbook_nums;
end
但是call的时候,有死循环,谁知道为什么?以及有没有更好的办法来解决,比如使用temporary table或别的什么方法。