mysql - query inside a query

2019-08-03 06:48发布

问题:

How is it possible to do the following : (i am using mysql - phpmyadmin )

this query returns a table name :

Select table_name from Table1 where id = 1

I want to use it inside another query , like :

select val from (Select table_name from Table1 where id = 1)

but this sure does not work, and since phpmyadmin does not support calling stored procedures, could there be any possible solution for this ?

回答1:

You cannot really do it in a single SQL statement.

You are trying to use data (field value) as metadata (table name), and SQL does not allow this.

You could break it in two statements or write dynamic SQL in a stored procedure. Note that not all client layers support returning resultsets from stored procedures.



回答2:

you also ma execute dynamic select:

declare v_table_name VarChar(128);
BEGIN 

Select table_name into v_table_name from Table1 where id = 1
SET @s = CONCAT('SELECT * FROM ', v_table_name, ' where id = 1'); 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1; 

END;