SQL派生表的结果在未知列中 [英] SQL derived table results in unknown column

问题描述

+------------------+        +------------------+    
| object           |        | example          |
+------------------+        +------------------+    
| id               |        | id               |    
| table_name       |        | ...              |    
| ref_id           |        |                  |    
+------------------+        +------------------+    

object  [ id = 5, table_name = example, ref_id = 2]
example [ id = 2 ]

我有一个表，该表可以表示源自许多不同表的一组对象. 这些对象都有一个id，由object.ref_id引用.

I have a table which can represent a group of objects originating from a number of different tables. These objects all have an id, referenced by object.ref_id.

我试图通过object.id选择原始对象(从object.table_name引用的表中).为此，我尝试使用以下SQL查询.尽管这样会导致#1054 - Unknown column 'entity.id' in 'where clause'.

I am trying to select the original object (from the table referenced by object.table_name) by object.id. For this, I am trying to use the following SQL query. Though this results in #1054 - Unknown column 'entity.id' in 'where clause'.

SELECT  * 
FROM    (
        SELECT  table_name
        FROM    object 
        WHERE   id = 5) AS entity
WHERE   entity.id = (
        SELECT  ref_id
        FROM    object 
        WHERE   id = 5)

我在做什么错了?

案例

SELECT * 
FROM example 
WHERE id = (
    SELECT  ref_id
    FROM    object 
    WHERE   id = 5 )

尽管如此，对于我而言，FROM子句并不像示例中那样容易，因为该值通常是从object.table_name派生的.

Although, for my case the FROM clause is not as easy as in the example, since this value is normally derived from object.table_name.

使用TOMBOM的方法进行更新

使用Tombom的解决方案会导致错误，为了进行测试，我替换了CONCAT函数中的变量.

Using the solution from Tombom results in an error, for the sake of testing I have replaced the variables in the CONCAT function.

SET @sql = CONCAT('SELECT * FROM ', 'example', ' WHERE id = ', '1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#1243-赋予EXECUTE的未知预准备语句处理程序(stmt)

#1243 - Unknown prepared statement handler (stmt) given to EXECUTE

推荐答案

您不能通过这种方式告诉MySQL查询(子查询)的结果是一个表名.您必须使用结果构建一个动态语句.

You can't tell MySQL that the result of a (sub)query is a table name this way. You'd have to build a dynamic statement with the result.

SELECT @tableName:=table_name, @refId:=ref_id FROM object WHERE id = 5 LIMIT 1;

SET @sql = CONCAT('SELECT * FROM ', @tableName, ' WHERE id = ', @refId);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

更新:请尝试使用存储过程:

UPDATE: Please have a try with a stored procedure:

DELIMITER $$
CREATE PROCEDURE procName(IN my_id INT)
BEGIN
SELECT @tableName:=table_name, @refId:=ref_id FROM object WHERE id = my_id LIMIT 1;

SET @sql = CONCAT('SELECT * FROM ', @tableName, ' WHERE id = ', @refId);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

然后使用以下命令执行存储过程

Then execute the stored procedure with

CALL procName(5);

这篇关于SQL派生表的结果在未知列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！