使用前缀为查询中的所有列别名
可以在选择中为所有列添加前缀别名吗? 我在 Oracle 数据库中有一组表,需要将它们连接在一起,并且大多数表具有相同的列名。我想要有类似的东西
select MAGIC_ADD_PREFIX("PREFIX", *) from TABLE
,并有像 ATM 这样的结果,
+---------+----------+----------+
|PREFIX_ID|PREFIX_FOO|PREFIX_BAR|
+---------+----------+----------+
|... | | |
我唯一能想到的是类似的东西,
select ID PREFIX_ID, FOO PREFIX_FOO, BAR PREFIX_BAR from TABLE
但它很难看并且容易出错
====进一步解释====
问题
select TABLE.*,...
是我使用 java + jdbc 驱动程序检索列,以及 java.sql.ResultSet
方法 (resultset.getInt("COLUMNNAME")
, .getString("COLUMNNAME") ")
...) 不支持语法“TABLENAME.COLUMNAME”。
如果我这样做(简化,没有错误检查...)
ResultSet rs = mkResultSet("select * from table_a, table_b");
rs.next();
System.out.println(rs.getInt("table_a.id"));
我会收到一个 SQLException
,其中包含无效的列名作为消息
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
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
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.ResultSet
methods (resultset.getInt("COLUMNNAME")
, .getString("COLUMNNAME")
...) doesn't support the syntax "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 SQLException
with invalid column name as message
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以这样做
或者您可以在基表上创建视图,例如
您可以通过从 user_tab_columns 选择列名来相当轻松地生成用于创建 a_vw 的 SQL
You can do
Or you could create views over the base tables like
You could generate the SQL for creating a_vw fairly easily from selecting the column name from user_tab_columns