How do I join a group of tables in a database with a similar name using a SQL query?

For example, I have a database called db and it has 100 tables called ta_1

... ta_100

respectively. Each table has two columns id(int)

and val(int)


  • If I want to get the total val, what should I do?
  • If I want to get a table as a result with two columns: tablename (String) and mean (float), what should I do?

source to share

1 answer

It goes without saying that you shouldn't have set up your database this way. There are a number of solutions, ranging from simple but mundane to complex but useful ones.

At some point, you need to create a list of tables and prepare a statement that concatenates them together. Copying and past using the database view would be a good start:

create view GlobalView (
    tabname varchar(10),
    id int,
    val int)
   select 'ta_1', id, val from ta_1
   select 'ta_2', id, val from ta_2
   select 'ta_3', id, val from ta_3
   select 'ta_100', id, val from ta_100


Then you can just run select:

select avg(1.0 * val) as mean
from GlobalView


An alternative would be to create a dynamic query that you can call either from an external program or using "dynamic SQL"



All Articles