我的数据库中有15个表。
我想对所有15个表运行一个select查询。类似这样的东西:
select count(*) from (SELECT Table_NAME FROM information_schema.tables
WHERE table_schema = 'my_table_schema' ) where ratings > 10;
看起来像这样的表(并非所有表都有ratings
列(:
Users Table
UserID | Rating
12345 | 23
12346 | 5
Movies Table
MovieID | Rating
44444 | 43
32345 | 43
12346 | 5
Federations Table
FederationID | Rating
22444 | 13
12345 | 130
45346 | 50
我正在寻找像这样的预期结果
Movies - 2
Users - 1
Federations - 3
...
...
...
编辑:将描述从3个表更新为15个表。
对于固定的表列表,可以使用union all
:
select 'movies' as who, count(*) as cnt from movies where rating > 10
union all select 'users', count(*) from users where rating > 10
union all select 'federations', count(*) from federations where rating > 10
如果您想动态地执行此操作,那么您需要一个准备好的语句。在MySQL中,它看起来像:
set @sql = null;
select @sql = group_concat(
'select ''', table_name, ''' as who, count(*) as cnt from `', table_name, '` where rating > 10'
separator ' union all '
)
from information_schema.tables
where table_schema = 'my_table_schema';
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
如果my_table_schema
中的任何表没有名为rating
的列,这将失败,这就是为什么我怀疑这真的是个好主意。