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?
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)
as
select 'ta_1', id, val from ta_1
UNION ALL
select 'ta_2', id, val from ta_2
UNION ALL
select 'ta_3', id, val from ta_3
UNION ALL
.....
UNION ALL
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"