菜鸟来问下ORACLE中怎么给一个视图加一个ID列,如何用rownum? 有人说用rownum,可是不知道具体的select语句这么写,能不能给小弟来个select那段加ID列的SQL代码 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 CREATE OR REPLACE VIEW view_test ASSELECT ROWNUM ID,e.deptno,d.dname FROM scott.emp e,scott.dept dWHERE d.deptno=e.deptno;SELECT * FROM view_test;输出:ID DEPTNO DNAME1 20 RESEARCH2 30 SALES3 30 SALES4 20 RESEARCH5 30 SALES6 30 SALES7 10 ACCOUNTING8 10 ACCOUNTING9 30 SALES10 30 SALES11 20 RESEARCH12 10 ACCOUNTING13 20 RESEARCH SELECT ROWNUM rn,v.* FROM view_test v;输出:RN ID DEPTNO DNAME1 1 20 RESEARCH2 2 30 SALES3 3 30 SALES4 4 20 RESEARCH5 5 30 SALES6 6 30 SALES7 7 10 ACCOUNTING8 8 10 ACCOUNTING9 9 30 SALES10 10 30 SALES11 11 20 RESEARCH12 12 10 ACCOUNTING13 13 20 RESEARCH /--试一下:select * from (selet rownum,t.* from test t where ...) where rn <10; select * from (selet rownum rn,t.* from test t where ...) where rn <10; select * from (select rownum rn,t.* from test t )t 帮我看一下这几个sql 怎么写? oracle 游标统计 请教一个函数! 新手求助IMP导入数据的问题 运行DBMS_SQL时出现错误ORA-06571 求助高手,Oracle数据库开发问题 请教,关于将图片写入表里的。 Oracle批量修改问题 求大神 急求 【求助】如何用Oracle的统计分析函数在一个语句中实现同比、累计同比 求助SQL语句高手 plsql developer连接中断的问题 为什么redhat linux 9 装了oracle10客户端,无法连接数据库
SELECT ROWNUM ID,e.deptno,d.dname FROM scott.emp e,scott.dept d
WHERE d.deptno=e.deptno;SELECT * FROM view_test;
输出:
ID DEPTNO DNAME
1 20 RESEARCH
2 30 SALES
3 30 SALES
4 20 RESEARCH
5 30 SALES
6 30 SALES
7 10 ACCOUNTING
8 10 ACCOUNTING
9 30 SALES
10 30 SALES
11 20 RESEARCH
12 10 ACCOUNTING
13 20 RESEARCH
输出:
RN ID DEPTNO DNAME
1 1 20 RESEARCH
2 2 30 SALES
3 3 30 SALES
4 4 20 RESEARCH
5 5 30 SALES
6 6 30 SALES
7 7 10 ACCOUNTING
8 8 10 ACCOUNTING
9 9 30 SALES
10 10 30 SALES
11 11 20 RESEARCH
12 12 10 ACCOUNTING
13 13 20 RESEARCH
/--试一下:select *
from (selet rownum,t.* from test t where ...)
where rn <10;
select *
from (selet rownum rn,t.* from test t where ...)
where rn <10;
from (select rownum rn,t.* from test t )t