MYSQL 查询在 LEFT JOIN 中使用变量作为表名

Question

SELECT var1,var2,var3,table_name 
FROM table1 LEFT JOIN table_name on var3=table_name.id

这意味着我想动态左连接表，具体取决于 table1 中的 table_name 值，因为 var3 是从那里获取的。

但上面的查询结果是

表table_name不存在

我的mysql限制错误？

Answer 1

表名以及列名在 SQL 查询中不能是动态的。因此，您必须以编程方式应用逻辑，使用 2 个查询或使用存储过程，请参阅此处的示例： http://forums.mysql.com/read.php?98,126506,126598#msg-126598

Answer 2

另一种方法是将所有表与联合查询联合起来：

SELECT *, 44 as table_origin FROM `Table_44`
UNION ALL
SELECT *, 58 as table_origin FROM `Table_58`;

您甚至可以将其准备为视图：

CREATE VIEW `AllTheTables` AS
SELECT *, 42 as table_origin FROM `Table_42`
UNION ALL
SELECT *, 44 as table_origin FROM `Table_44`
UNION ALL
SELECT *, 58 as table_origin FROM `Table_58`
UNION ALL
SELECT *, 69 as table_origin FROM `Table_69`;

从而安全地查询它：

SELECT * FROM AllTheTables WHERE table_origin IN (44,58) AND something = 'foobar';
-- or --
SELECT * FROM AllTheTables WHERE table_origin = 42 AND something = 'the question';

---

在你的具体情况下，它可能看起来像这样：

SELECT var1, var2, var3, table_name 
FROM table1 LEFT JOIN AllTheTables ON table1.var3=AllTheTables.table_origin