选择使用动态生成的表名 [英] Select using dynamically generated tablename
问题描述
我遇到以下问题.我的表(geo_table
)结构如下:
I've got the following problem. My table (geo_table
) structure is as follows:
foreign_table_id | foreign_table_name | some_other_fields...
foreign_table_name
存储不同表的名称,而foreign_table_id
存储这些表中的ID.我想做的是检查上述表中的行是否存储了对其他表的有效引用.因此,我需要从外部表中选择一行,其名称基于存储在foreign_table_name
中的字符串.我的代码无法正常工作,因为它存在语法错误,但可以清楚地表达出我想要实现的目标:
foreign_table_name
stores names of different tables and foreign_table_id
stores ids within these tables. What I want to do is check whether a row in the above mentioned table stores valid reference to some other table. Thus I need to select a row from foreign table which name is based on string stored in foreign_table_name
. My code didn't work because it has syntax error but it clearly presents what I wanted to achieve:
SELECT foreign_table_id, foreign_table_name FROM `geo_table` gt
WHERE (
SELECT COUNT(*) FROM gt.foreign_table_name AS t
WHERE gt.foreign_table_id = t.uid
) > 0
推荐答案
如果您知道所有可能的表名,则可以使用条件语法来实现它:
If you know all possible table names then you can implement it using conditional syntax:
SELECT foreign_table_id, foreign_table_name FROM `geo_table` gt
WHERE
CASE gt.foreign_table_name
WHEN 'table1' THEN
EXISTS (
SELECT * FROM table1
WHERE id = gt.foreign_table_id
)
WHEN 'table2' THEN
EXISTS (
SELECT * FROM table2
WHERE id = gt.foreign_table_id
)
ELSE
FALSE
END
这篇关于选择使用动态生成的表名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!