比如 有这些表
tbl_100
tbl_103
tbl_97
tbl_16
tbl_29
tbl_99
...只知道表名是tbl前缀,但是想 union多个表的查询结果怎么写sql语句啊??
例如
select sum(pn) from (
select count(*) pn from tbl_??? union
select count(*) pn from tbl_??? union
...
select count(*) pn from tbl_???)tmp_tbl;google了下似乎找不到答案请高手们指点指点
tbl_100
tbl_103
tbl_97
tbl_16
tbl_29
tbl_99
...只知道表名是tbl前缀,但是想 union多个表的查询结果怎么写sql语句啊??
例如
select sum(pn) from (
select count(*) pn from tbl_??? union
select count(*) pn from tbl_??? union
...
select count(*) pn from tbl_???)tmp_tbl;google了下似乎找不到答案请高手们指点指点
解决方案 »
- mysql登入时出错ERROR 2003:Can't connect to MySQL server on 'localhost'<10061>
- 有没有能替代power builder的免费开源开发软件
- mysql语句问题!
- 如何查询字段关键字来着??
- 关于InnoDB_buffer_pool什么时候写入到文件中
- mysql到底还是不是免费的?
- 如何把表中的姓名有重复的数据都列出来 急,谢谢
- mysql的limit问题
- 多条件查询,帮忙写一个语句
- mysql.users里面, root用户的 host设为 % ,所有机器都可以连接; 那么,怎么能“只让特定的10多个IP访问、使用mysql呢” ?
- mysql的显示格式
- Error: Can't connect to MySQL server on 'localhost' (10057)
execute ...
drop prepare...
先取得所有以TBL开关的表名,再循环、字符累加形成SQL语句,执行sELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'DATABASE' AND table_name LIKE 'tbl_%'
delimiter //
create procedure getcount(in start int,in end int)
begin
declare tmp int;
declare sqlstr text default '';
set tmp = start;while tmp <= end do
if tmp = end then
set sqlstr = concat(sqlstr,'select count(*) pn from tbl_',tmp);
else
set sqlstr = concat(sqlstr,'select count(*) pn from tbl_',tmp,' union ');
end if;
set tmp = tmp + 1;
end while;set @str = concat('select sum(pn) from(',sqlstr,')tmp');prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
end
//
delimiter ;
connect database;
$result=mysql_query("show tables from db_name like 'tbl_%'");
foreach($result as $value){
$sql 连接
}
mysql_query("$sql");
?>