oracle 使用前缀为查询中的所有列别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2501017/
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
alias all column in a query with a prefix
提问by Vito De Tullio
it's possible to alias all column with a prefix, in a select? I have a group of tables in an Oracle db that I need to join together, and most of them have the same column names. I would like to have something like
是否可以在选择中使用前缀为所有列添加别名？我在 Oracle 数据库中有一组表，我需要将它们连接在一起，其中大多数表具有相同的列名。我想要类似的东西
select MAGIC_ADD_PREFIX("PREFIX", *) from TABLE
and have a result like
并有这样的结果
+---------+----------+----------+
|PREFIX_ID|PREFIX_FOO|PREFIX_BAR|
+---------+----------+----------+
|... | | |
ATM the only thing I can think is something chumsky like
ATM 我唯一能想到的就是像笨蛋一样的东西
select ID PREFIX_ID, FOO PREFIX_FOO, BAR PREFIX_BAR from TABLE
but it's ugly as hell and error-prone
但它丑得像地狱一样容易出错
==== further explanation ====
==== 进一步解释 ====
The problem with
问题与
select TABLE.*,...
is that I'm using java + jdbc drivers to retrieve the columns, and the java.sql.ResultSetmethods (resultset.getInt("COLUMNNAME"), .getString("COLUMNNAME")...) doesn't support the syntax "TABLENAME.COLUMNAME".
是我使用 java + jdbc 驱动程序来检索列，并且java.sql.ResultSet方法 ( resultset.getInt("COLUMNNAME"), .getString("COLUMNNAME")...) 不支持语法“TABLENAME.COLUMNAME”。
if I do (simplified, no error cheks...)
如果我这样做（简化，没有错误检查......）
ResultSet rs = mkResultSet("select * from table_a, table_b");
rs.next();
System.out.println(rs.getInt("table_a.id"));
I get a SQLExceptionwith invalid column name as message
我收到一个SQLException列名无效的消息
回答by Gary Myers
You can do
你可以做
select a.*, b.* from table_a a, table_b b where.....
Or you could create views over the base tables like
或者您可以在基表上创建视图，例如
create view a_vw as select a.col1 a_col1, a.col2 a_col2...
You could generate the SQL for creating a_vw fairly easily from selecting the column name from user_tab_columns
您可以通过从 user_tab_columns 中选择列名来相当容易地生成用于创建 a_vw 的 SQL
