Oracle:使用“自动增量ID"列创建视图 [英] Oracle: Create a View with Auto Increment id column

问题描述

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

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文件.因此在我的XML生成应用程序中，某些过程需要primary key column.

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添加到我所有的select语句中.但是它返回了重复的ID.因为每个选择语句中的rownum从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.但是我不能使用序列，因为我的select语句具有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.

有什么办法吗?

推荐答案

您可以对联合进行选择，例如:

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:使用“自动增量ID"列创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！