请教各位:表名为:
http_20090901
http_20090902
......
http_20090930
这些表的结构都一样。想从里边查一个字段(name)的值为: http://scholar.google.com.hk怎么能连表查询,不要我一个一个去查?
http_20090901
http_20090902
......
http_20090930
这些表的结构都一样。想从里边查一个字段(name)的值为: http://scholar.google.com.hk怎么能连表查询,不要我一个一个去查?
解决方案 »
- mysql的读取性能
- mysql root 权限消失
- mysql 5.0.21-log 如何选择存储引擎?
- 如何让结果按照原表中的顺序显示?
- (非常着急)请问IF语句能不能放在where里面
- 关于把中文插到postgres数据库的问题!!!!!!!
- [Mysql]谁帮我解决这个中文问题,100分相送!不够还可以再加!
- /usr/libexec/mysqld: Table 'mysql.host' doesn't exist
- ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 438, event
- 我的MySQL数据库里面明明存在一个表,可是查看表的时候又显示不存在是怎么回事?
- mysql 6 连不上
- 大家帮帮忙,自定义函数报不能递归的错误,怎么解决?
select * from http_20090902 union all
..
DROP PROCEDURE IF EXISTS mydb.Clear_DB;
CREATE PROCEDURE mydb.`Clear_DB`(
)
BEGIN
DECLARE done INT DEFAULT 0; #游标的标志位
DECLARE a varchar(20);
DECLARE b varchar(20);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_name like 'http_200909%' ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a;
IF NOT done THEN
set b=concat(b,'select * from ',a,' where name=http://scholar.google.com.hk union all'); # 拼 命令
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET @E=mid(b,1,length(b)-9); # 去掉最后一个UNION ALL
PREPARE stmt1 FROM @E;
EXECUTE stmt1; # 执行命令
DEALLOCATE PREPARE stmt1; #释放对象
END;
delimiter //
DROP PROCEDURE IF EXISTS mydb.Clear_DB;
CREATE PROCEDURE mydb.`Clear_DB`(
)
BEGIN
DECLARE done INT DEFAULT 0; #游标的标志位
DECLARE a varchar(20);
DECLARE b varchar(20);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_name like 'http_200909%' ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a;
IF NOT done THEN
set b=concat(b,'select * from ',a,' where name=http://scholar.google.com.hk union all'); # 拼 命令
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SET @E=mid(b,1,length(b)-9); # 去掉最后一个UNION ALL
PREPARE stmt1 FROM @E;
EXECUTE stmt1; # 执行命令
DEALLOCATE PREPARE stmt1; #释放对象
END;
//
delimiter ;
union all
select * from http_20090902 where name ='http://scholar.google.com.hk'
union all
select * from http_20090903 where name ='http://scholar.google.com.hk'
union all
...
union all
select * from http_20090930 where name ='http://scholar.google.com.hk'