BEGIN
#定义变量,先定义再引用,引用时@+变量名
declare city varchar(100);declare device varchar(10);
declare table varchar(50);
declare sqlstr varchar(100);
declare i int;
declare u varchar(10);
#为变量赋值
set @device='jsj_';
set @city='sz,gz,fs,dg,zs,hz,jm,st,zh,jy,zj,mm,zq,qy,cz,mz,sg,hy,yj,sw,yf';
set @sqlstr='';
set @u='';
set i=1;
while i<62 do
#substring(@city1,i,2),数据库没有数组的写法,通过分切得到每一个关键词,拼接表名
set @table=concat(@device,substring(@city1,i,2));#拼接sql语句,循环查询表,21个不同城市的表
set @sqlstr=concat(@sqlstr,@u,' SELECT * from ',@table);
set @u='union all';
set i=i+3;
end while;
SET @sqlstr=concat('CREATE TABLE RESULT AS', @sqlstr);
#执行sql语句
prepare stmt from @sqlstr;
EXECUTE stmt; #Routine body goes here...
END