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屋!