Oracle:创建一个带有 Auto Increment id 列的视图 [英] Oracle: Create a View with Auto Increment id column

问题描述

我创建了一个 view 来填充来自不同表的数据.我使用了 10 个 select 语句，并使用 UNION ALL 组合了这些 select 语句的结果.

I have created a view that fills data from different tables. I used 10 select statements and combine the results of those select statements using UNION ALL.

我想将 primary key column 添加到我的视图中.因为我必须使用此 view 中的数据创建 XML 文件.所以我需要一个 primary key column 用于我的 XML 构建应用程序中的某些过程.

I want to add primary key column to my view. because I have to create XML file using data in this view. so I need a primary key column for some process in my XML building application.

我已将 rownum 添加到我所有的选择语句中.但它返回了重复的 ID.因为rownum在每个select语句中都是从1开始的.

I have add rownum to all my select statements. But it returned duplicate ids. because rownum in each select statements start from 1.

然后我创建了一个序列并尝试使用 nextval .但我不能使用序列，因为我的选择语句有 group by 和 order by.

Then I have created a sequence and tried use nextval . But I can't use sequence because my select statements has group by and order by.

有没有办法做到这一点?

Is there any way to do that ?

推荐答案

你可以在联合上做一个选择，例如:

You can do a select over the union, for example:

SELECT rownum(),*
FROM (SELECT * FROM tableA UNION ALL SELECT * FROM tableB)

更新

SELECT rownum, t.*
FROM (SELECT * FROM tableA UNION ALL SELECT * FROM tableB) t

这篇关于Oracle:创建一个带有 Auto Increment id 列的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！