Oracle 自动重命名 select 中的列？

Oracle rename columns from select automatically?

提问by bwawok

I have 2 tables with the following fields.

我有 2 个包含以下字段的表。

Table1

表格1

• AA
• BB
• CC
• DD

• 机管局
• BB
• 抄送
• DD

Table2

表2

• AA
• CC
• EE

• 机管局
• 抄送
• EE

Query

询问

Select t1.*, 
       t2.*
  from table1 t1,
  join table2 t2 on table1.DD = table2.EE

My data columns back with the following column names:

我的数据列返回以下列名称：

AA, BB, CC, DD, **AA_1**, **CC_1**, EE

I don't want the column names like that. I want them to have the table name prefixed in the names of common (or all columns). I could fix this with:

我不想要那样的列名。我希望他们在公共（或所有列）的名称中添加表名作为前缀。我可以解决这个问题：

select t1.AA as t1_AA, t1.BB as t1_BB, t1.CC as t1_CC, t1.DD as t1_DD, 
 t2.AA as t2_AA, t2.CC as t2_CC, t2.EE as t2_EEE
   from table1 t1,
    inner join table2 t2
    on table1.DD = table2.EE

But that means every select everywhere becomes 500 lines longer. Is there a magic way to do this in oracle? Basically I want to write my code like

但这意味着每个地方的每个选择都会变长 500 行。在 oracle 中是否有一种神奇的方法可以做到这一点？基本上我想写我的代码

 select t1.* as t1_*, t2.* as t2_*
       from table1 t1,
        inner join table2 t2
        on table1.DD = table2.EE

But of course that is not valid SQL

但当然这不是有效的 SQL

回答by OMG Ponies

Is there a magic way to do this in oracle?

在 oracle 中是否有一种神奇的方法可以做到这一点？

Not that I'm aware of. Your options amount to:

不是我所知道的。您的选择相当于：

1. Address the column naming scheme - you'd need to use ALTER TABLE statements like:

   ALTER TABLE table_name
        RENAME COLUMN old_name to new_name;
   

2. Use column aliases

1. 解决列命名方案 - 您需要使用 ALTER TABLE 语句，例如：

   ALTER TABLE table_name
        RENAME COLUMN old_name to new_name;
   

2. 使用列别名

You could use views to save on the work & effort of defining column aliases, but it's not a recommended practice because of the bad performance when layering views on top of one another.

您可以使用视图来节省定义列别名的工作和精力，但这不是推荐的做法，因为将视图分层放置时性能不佳。

回答by vls

In Oracle SELECTsyntax, there is currently no way to assign column aliases to multiple columns based on some expression. You have to assign an alias to each individual column.

在 OracleSELECT语法中，目前无法根据某些表达式为多个列分配列别名。您必须为每个单独的列分配一个别名。

回答by DCookie

Is creating a view an option?

创建视图是一种选择吗？

What is the software you're using that does this to you? I don't see this behavior in SQL*Plus or PL/SQL Developer in 10g. PL/SQL won't let you build a cursor with this ambiguity in it.

您正在使用的软件是什么？我在 10g 中的 SQL*Plus 或 PL/SQL Developer 中没有看到这种行为。PL/SQL 不会让您构建具有这种歧义的游标。

回答by Badmiral

Try this

尝试这个

 select t1.AA "t1_AA", t2.AA "t2.AA"
 from table1 t1,
 inner join table2 t2
 on table1.DD = table2.EE

As he said before, you need to do it per column

正如他之前所说，你需要每列都做