You Daniel, please have a look at the following SQL, the first Union ALL take out, then automatically perform concat out as a result, how to implement? Post the script for the stored procedure, thank you!

CodePudding user response:

Need to write two statements, sequentially, the first sentence is to define your statement, the second sentence is executed, the sample code is as follows:
PREPARE stmt1 FROM 'select * FROM t. table_name t';
The EXECUTE stmt1;

If you need the incoming parameters, a sample, the question mark is the location of the variables, define the number of variables and the question mark to equal, according to the order, question marks, corresponding to a variable is the sample code is as follows:
PREPARE stmt1 FROM 'select * FROM t. table_name where t t.i d=? And t.n ame=? ';
The SET @ a=3;
The SET @ b=4;
The EXECUTE stmt1 USING @ a, @ b;

CodePudding user response:

reference 1st floor AHUA1001 response:

need to write two statements, sequentially, the first sentence is to define your statement, the second sentence is executed, the sample code is as follows:
PREPARE stmt1 FROM 'select * FROM t. table_name t';
The EXECUTE stmt1;

If you need the incoming parameters, a sample, the question mark is the location of the variables, define the number of variables and the question mark to equal, according to the order, question marks, corresponding to a variable is the sample code is as follows:
PREPARE stmt1 FROM 'select * FROM t. table_name where t t.i d=? And t.n ame=? ';
The SET @ a=3;
The SET @ b=4;
The EXECUTE stmt1 USING @ a, @ b;

HI, AHUA1001
SELECT CONCAT (' union all SELECT "', TABLE_SCHEMA, ' ' 'as the db,' ' ', TABLE_NAME, ' ' 'as tbname, count (1) the as ro', TABLE_SCHEMA, '. ', TABLE_NAME) as SqlExe FROM information_schema. The TABLES as t WHERE t.T ABLE_TYPE='BASE TABLE AND t.T ABLE_SCHEMA=' test '
This is the SQL statement, found out the result is
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| SqlExe |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| union all select 'test' as the db, 'accesslog as tbname, count (1) the as ro test. The accesslog |
| union all select 'test' as the db, 't' as tbname, count (1) the as ro test. T |
| union all select 'test' as the db, 't1' as tbname, count (1) the as ro test. T1 |
| union all select 'test' as the db, 't2' as tbname, count (1) the as ro test. The t2 |
| union all select 'test' as the db, 'temp' as tbname, count (1) the as ro test. \ |
| union all select 'test' as the db, 'tn as tbname, count (1) the as ro test. Tn |
| union all select 'test' as the db, 'tt as tbname, count (1) the as ro test. Tt |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
7 rows in the set (0.00 SEC)
Now demand is, will found out (above) the result of the first union all take out, carry out the content behind the independently, as follows, please help, thank you!
Select 'test' as the db, 'accesslog as tbname, count (1) the as ro test. The accesslog
Union all select 'test' as the db, 't' as tbname, count (1) the as ro test. T
Union all select 'test' as the db, 't1' as tbname, count (1) the as ro test. T1
Union all select 'test' as the db, 't2' as tbname, count (1) the as ro test. The t2
Union all select 'test' as the db, 'temp' as tbname, count (1) the as ro test. The temp
Union all select 'test' as the db, 'tn as tbname, count (1) the as ro test. Tn
Union all select 'test' as the db, 'tt as tbname, count (1) the as ro test. Tt

CodePudding user response:

This simple, you want the result is a lot of union all right,
Is a select query,
The following, null, null... "In the number of null, and the large number of columns in the query you,

Select the null null... The from dual where 1=2 union all
Here to write you that a lot of statements,

Thinking and on the contrary, you want to get rid of the former union all, I am in the former union all plus a thing, and add this thing where 1=2, does not show,

Also feel you this return as a result, there is a problem, may not be able to perform in the past, you debug it, there is a problem to reply again,