Oracle:创建一个带有 Auto Increment id 列的视图
我创建了一个 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
相关文章