56,679
社区成员
发帖
与我相关
我的任务
分享mysql表字段查询
下文中的?表示数字，我有一连串realtime_data?的表，这些表是动态生成的，在每个表中有AD?_ID这样的字段，AD?_ID字段有6个，现在我想写一个存储过程，想实现如下功能，
1、计算并列出每个表中AD?_ID值为00的表字段的个数
mysql高手求帮助
1、计算并列出每个表中AD?_ID值为00的表字段的个数
mysql高手求帮助
...全文
请发表友善的回复…
发表回复
南宫曲直 2015-02-13
- 打赏
- 举报
代码：
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
...
由于代码不能拷过来，是手工编写，可能存在一些错误
