解决方案 »
- 同一个字段的2次排序问题~
- MYSQL是否支持for 循环等语句
- 如何只输出字段不为空的字段??
- 建表错误:MySQL said: You have an error in your SQL syntax near '( agegrp smallint(5) DEFAULT '0' NOT NULL, sex char(20) DEFAULT
- 如何解决搜索条件中某字段含有空格
- mysql如何分类统计数量,并以图标形式输出。
- 删除表格出现:MySQL server has gone away 如何解决!!!
- 请问这个SQL语句怎么优化
- MySql如何将两个查询结果放入一个结果集
- 各位sql大神,帮我一把
- 用SQL SERVER2008每天插入100W数据查询100W次,请教各路大神应该如何设计表?
- 新人求助DBA菊苣们一个问题,关于自关联与计数的
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
...由于代码不能拷过来,是手工编写,可能存在一些错误