oracle 使用子查询而不是表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5931549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using subquery instead of the tablename
提问by zhanjian
Table Meta:
-------------------------------------
type tab_name
new tab_news
sports tab_sps
Table tab_news
------
id
Table tab_sps
-------------------
id
xx
Now I want to use
现在我想用
SELECT id
FROM (SELECT tab_name
FROM Meta
WHERE type = 'news');
But it does not work, any ideas?
但它不起作用，有什么想法吗？
采纳答案by OMG Ponies
SQL does not support a variable/etc for the table name -- the only means of supporting what you ask is by using dynamic SQL:
SQL 不支持表名的变量/等 - 支持您所要求的唯一方法是使用动态 SQL：
FOR i IN (SELECT tab_name
FROM META m
WHERE m.type = ?) LOOP
EXECUTE IMMEDIATE 'SELECT * FROM '|| i.tab_name ||'';
END LOOP;
回答by Gary Myers
The syntax structure you are trying to use doesn't do what you want. What appears in the FROM clause is a data set. This might be a table or a view. In your case the data set is a subset of "Meta"; specifically the column "tab_name" for rows with the type of "news".
您尝试使用的语法结构不符合您的要求。FROM 子句中出现的是一个数据集。这可能是表或视图。在您的情况下，数据集是“元”的子集；特别是“新闻”类型的行的“tab_name”列。
SELECT id
FROM (SELECT tab_name
FROM Meta
WHERE type = 'news');
SQL is basically set oriented. You seem to want the "tab_name" to return a 'pointer' or a reference to a data set. That suggests a more object oriented approach. Rather than a table_name, the select from Meta would return instances of an object and the wrapper would use a method on that object to extract the details. That would be more of
SQL 基本上是面向集合的。您似乎希望“tab_name”返回“指针”或对数据集的引用。这表明了一种更面向对象的方法。而不是 table_name，来自 Meta 的选择将返回一个对象的实例，包装器将使用该对象上的方法来提取详细信息。那会更多
SELECT tab_name.getId()
FROM Meta
Where type = 'news';
But I'd need a more 'business terms' description of the problem before trying to guess what the object structures might look like.
但是在尝试猜测对象结构可能是什么样子之前，我需要对问题进行更多的“业务术语”描述。
回答by IAmTimCorey
I do not believe what you are trying to achieve is possible. If you are working with a programming language with this data, you could return the sub-query value first and then build a new SQL statement for the query you want. However, building a dynamic query inside SQL like this does not appear to be possible.
我不相信你想要实现的目标是可能的。如果您正在使用具有此数据的编程语言，您可以先返回子查询值，然后为您想要的查询构建一个新的 SQL 语句。但是，像这样在 SQL 中构建动态查询似乎是不可能的。
I think you need to take a step back and look at your database logic. There has to be a different way of doing this. For example, since each table had to have the same layout, maybe you could do a union all and then filter the data to only what you really want. You could do that at runtime with a sub-query. The process would have a significant overhead if it scaled to much, but it might solve your root issue. Basically, rethink your design. There is a way to accomplish your end goal but it isn't down this path.
我认为您需要退后一步，看看您的数据库逻辑。必须有不同的方法来做到这一点。例如，由于每个表必须具有相同的布局，也许您可以将所有数据合并，然后将数据过滤为您真正想要的数据。您可以在运行时使用子查询执行此操作。如果该过程扩展到很多，则会产生大量开销，但它可能会解决您的根本问题。基本上，重新考虑您的设计。有一种方法可以实现您的最终目标，但不是沿着这条路走的。
回答by yfeldblum
Try aliasing the subquery.
尝试别名子查询。
select * from (select tab_name from Meta where type='news') as my_sub_query;
