请教各位:表名为:
http_20090901
http_20090902
......
http_20090930
这些表的结构都一样。想从里边查一个字段(name)的值为: http://scholar.google.com.hk怎么能连表查询,不要我一个一个去查?
http_20090901
http_20090902
......
http_20090930
这些表的结构都一样。想从里边查一个字段(name)的值为: http://scholar.google.com.hk怎么能连表查询,不要我一个一个去查?
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'