代码: declare m_sql varchar(2000); declare m_tablename varchar(50); declare m_index tinyint; declare m_num tinyint;set @num=0; set m_index=1;drop temporary table if exists tmp_result; create temporary table if not exists tmp_result(num tinyint,tablename varchar(50),count1 int,count2 int,count3 int,count4 int,count5 int,count6 int) engine=heap;insert into tmp_result select @num:=@num+1,TABLE_NAME,0,0,0,0,0,0 from information_schema.TABLES where TABLE_NAME like concat('realtime_data_','%');select max(num) into m_num from tmp_result;while m_index<=m_num do select tablename into m_tablename from tmp_result where num=m_index;
set m_sql=concat('update tmp_result B inner join ( select ''',m_tablename,''' as tablename,ifnull(sum(a.num1),0) as count1,ifnull(sum(a.num2),0) as count2, ifnull(sum(a.num3),0) as count3,ifnull(sum(a.num4),0) as count4, ifnull(sum(a.num4),0) as count5,ifnull(sum(a.num6),0) as count6 from( select if(AD1_ID=0.00,1,0) as num1,if(AD2_ID=0.00,1,0) as num2,if(AD3_ID=0.00,1,0) as num3, if(AD4_ID=0.00,1,0) as num4,if(AD5_ID=0.00,1,0) as num5,if(AD6_ID=0.00,1,0) as num6 from ',m_tablename,' ) A )AA ON B.tablename=''',m_tablename,''' set b.count1=aa.count1, b.count2=aa.count2, b.count3=aa.count3, b.count4=aa.count4, b.count5=aa.count5, b.count6=aa.count6'); set @sql=m_sql; prepare stmt from @sql; execute stmt;
set m_index=m_index+1; end while;select * from tmp_result;drop temporary table if exists tmp_result;验证可以得到结果: 结果为:num,tablename,count1,count2,count3,count4,count5,count6 1,'realtime_data_1',0,8,4,2,4,0 ...由于代码不能拷过来,是手工编写,可能存在一些错误
declare m_sql varchar(2000);
declare m_tablename varchar(50);
declare m_index tinyint;
declare m_num tinyint;set @num=0;
set m_index=1;drop temporary table if exists tmp_result;
create temporary table if not exists tmp_result(num tinyint,tablename varchar(50),count1 int,count2 int,count3 int,count4 int,count5 int,count6 int) engine=heap;insert into tmp_result
select @num:=@num+1,TABLE_NAME,0,0,0,0,0,0
from information_schema.TABLES
where TABLE_NAME like concat('realtime_data_','%');select max(num) into m_num from tmp_result;while m_index<=m_num do
select tablename into m_tablename from tmp_result where num=m_index;
set m_sql=concat('update tmp_result B
inner join (
select ''',m_tablename,''' as tablename,ifnull(sum(a.num1),0) as count1,ifnull(sum(a.num2),0) as count2,
ifnull(sum(a.num3),0) as count3,ifnull(sum(a.num4),0) as count4,
ifnull(sum(a.num4),0) as count5,ifnull(sum(a.num6),0) as count6
from( select if(AD1_ID=0.00,1,0) as num1,if(AD2_ID=0.00,1,0) as num2,if(AD3_ID=0.00,1,0) as num3,
if(AD4_ID=0.00,1,0) as num4,if(AD5_ID=0.00,1,0) as num5,if(AD6_ID=0.00,1,0) as num6
from ',m_tablename,' ) A
)AA ON B.tablename=''',m_tablename,'''
set b.count1=aa.count1,
b.count2=aa.count2,
b.count3=aa.count3,
b.count4=aa.count4,
b.count5=aa.count5,
b.count6=aa.count6'); set @sql=m_sql;
prepare stmt from @sql;
execute stmt;
set m_index=m_index+1;
end while;select * from tmp_result;drop temporary table if exists tmp_result;验证可以得到结果:
结果为:num,tablename,count1,count2,count3,count4,count5,count6
1,'realtime_data_1',0,8,4,2,4,0
...由于代码不能拷过来,是手工编写,可能存在一些错误