Create MySQL view with variables

Question

I want to create a view out of values from different tables. After 2 day of researching without a result I'm now asking for some hints.

This is the table op_id which stores only operationID's

|  op_id  |
|---------|
| 20180101|
| 20180102|
|      ...|

For each op_id in op_id - table exists a own table. For example operations_20180101, operations_20180102, ...

The content of each table looks

| username | data |
|----------|------|
|  john    |  1239|
|  adam    |   857|
|       ...|   ...|

What I try to do is, create a view with the sum(data) of all single tables:

| 20180101 |  3746|
| 20180102 |  4765|
|       ...|   ...|

To create a simple SQL statement

SELECT * FROM operationview

to get all the data I need.

With

SELECT op_id FROM op_id

I get all my op_id's.

But from there I don't know how to store the result in a variable and query the corresponding table to create a view like

for op_id in (SELECT op_id FROM op_id):
    CREATE VIEW overview (SELECT op_id, sum(data) FROM operations_op_id)

All I got from MySQL Documentation looks simple, but doesn't solve my problem. It's the first time I try to do more complex stuff than querying and subquerying. So please excuse my question if the solution is simpler then I can figure out.

Answer 1

You are possibly looking for dynamic sql where you build code to be executed. For example given

drop table if exists op_id,operations_20180101 ,operations_20180102  ;

create table op_id (op_id  int);
insert into op_id values
(20180101),
(20180102);

create table operations_20180101(username varchar(3) ,data int);
create table operations_20180102(username varchar(3) ,data int);

insert into operations_20180101 values 
('aaa',1),('bbb',2);

insert into operations_20180102 values 
('aaa',10),('bbb',20);

You can build a sql statement like this

set @sql = 
(select
replace 
(
(select 
 group_concat(concat('select ', op_id, ',sum(data) as sumdata from ',
 concat('operations_',op_id) ,
 ' union ')
 )
from
(select distinct op_id from op_id) s
) 
,'union ,','union '
)
)
;
set @sql = concat(Substring(@sql,1,length(@sql) -7),';');

Which unions all the defined tables and look like this

select 20180101,sum(data) as sumdata from operations_20180101 
union 
select 20180102,sum(data) as sumdata from operations_20180102;

which can then be passed to sql to execute like this

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

result

+----------+---------+
| 20180101 | sumdata |
+----------+---------+
| 20180101 |       3 |
| 20180102 |      30 |
+----------+---------+
2 rows in set (0.00 sec)