需求：用存盤程序把一張表里的資料(不同的時間取的是不同的表，如2017上半年取的是tableName2017A,下半年就取的是tableName2017B)，然后插入另一張表。
如果不是變數，則是以下寫法:
create procedure findAverageTime()
begin
declare hasNext int default 1;
declare row_insertTime timestamp;
declare row_username varchar(50);
declare row_averageTime int(6);
declare cursorName cursor for
select insertTime,
username,
avg(waitTime)"averageTime"
from
tb_order2017B
where
status = '1'
and
finished = '2'
and
waitTime != '0'
and
date_format(inserttime ,'%Y-%m-%d') = date_format((select date_sub(now(),interval 1 day)),'%Y-%m-%d')
group by
username;
declare exit handler for NOT FOUND set hasNext := 0;
open cursorName;
repeat
fetch cursorName into row_insertTime ,row_username ,row_averageTime;
insert into averageTime values(row_insertTime ,row_username ,row_averageTime);
until hasNext=0 end repeat;
close cursorName;
end$
現在需要根據時間來確定取資料的表，也就是上面的tb_order2017B需要寫活。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/110896.html
標籤：MySQL