如何从表中选择所有列，以及其他列(如 ROWNUM) ？

在 Oracle 中，可以执行 SELECT语句，将行号作为结果集中的列返回。

比如说,

SELECT rownum, column1, column2 FROM table

报税表:

rownum       column1       column2
1            Joe           Smith
2            Bob           Jones

但是我不想手工指定每个列，我想这样做:

select rownum,* from table

rownum       column1       column2       column3       column4
1            Joe           Smith         1             2
2            Bob           Jones         3             4

有什么想法吗？

100834 次浏览

小开

最佳答案

Qualify the * with the name of the table:

select rownum, table.* from table

小开

Dave's answer is great, i'd just like to add that it's also possible to do that by placing the wildcard as the first column:

select *,rownum from table

Works, but the following won't:

select rownum,* from table

I've tested on MySQL.

小开

Unfortuantely, i dont think therei s a way to do it, easiest is probably inner join with itself with an inline table of id,count(*), and put an outer select statement

小开

Dave's answer did not work for me on Oracle 11g (table.* without alias). The following worked:

select rownum, t.* from table t