我想在select 查询语句的结果集中,增加一个id字段,标记记录的顺序号,怎么办呢? select name,age from person结果集是:a,22b,33我希望得到的结果集是:1,a,222,b,333,....4,.......第一列序号字段希望从select 语句中产生,怎么办的? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select rownum,name,age from person; 用rownum在表中可以,我现在从视图中查询的,怎么办? 也一样...rownum是伪列...只跟结果集走...不管数据从何而来 一个用法,没有区别啊.CREATE OR REPLACE VIEW v_test AS SELECT emp.empno,dept.deptno FROM emp,dept WHERE emp.deptno=dept.deptno;SELECT ROWNUM,x.* FROM v_test x;输出:ROWNUM EMPNO DEPTNO1 7369 202 7499 303 7521 304 7566 205 7654 306 7698 307 7782 108 7788 209 7839 1010 7844 3011 7876 2012 7900 3013 7902 2014 7934 10 我也试了下,证明是可以的create view test_view as select a.dno,a.dname,a.dnum,b.eno,b.ename,b.eage from test_dept a,test_emp b where a.dno=b.dnoselect rownum,dno from test_view结果:1 1 102 2 103 3 104 4 205 5 206 6 207 7 308 8 309 9 30 方法二:select row_number() over(order by name) as rn,name,age from person; 方法二:select row_number() over(order by name) as rn,name,age from person; The first Method:select rownum,name,age from person; The second Method:select row_number() over (order by 1) as no,name,age from person; orcale基础 请教一个SQL语句问题 oracle创建表的时候,如何实现像mysql中的autoincrement功能 精华推荐:专家分享Oracle数据库业务优化心得 装完8i(8.1.6.7)后为什么没有diagnostics pack? 登录到ORACLE MANAGER SRRVRE?? 讨论:买的oracle和从网上下载的oracle有什么不同? 这些表怎么用SQL写出来!!!!!!!!!!!!! Oracle数据库服务器本级远程的plsql都能脸上但是直接命令sqlplus不行 求 大佬 帮忙 求解一个多表查询语句 Oracle 问题
也一样...rownum是伪列...只跟结果集走...不管数据从何而来
CREATE OR REPLACE VIEW v_test AS SELECT emp.empno,dept.deptno FROM emp,dept WHERE emp.deptno=dept.deptno;
SELECT ROWNUM,x.* FROM v_test x;
输出:
ROWNUM EMPNO DEPTNO
1 7369 20
2 7499 30
3 7521 30
4 7566 20
5 7654 30
6 7698 30
7 7782 10
8 7788 20
9 7839 10
10 7844 30
11 7876 20
12 7900 30
13 7902 20
14 7934 10
create view test_view as
select a.dno,a.dname,a.dnum,b.eno,b.ename,b.eage
from test_dept a,test_emp b
where a.dno=b.dno
select rownum,dno from test_view
结果:
1 1 10
2 2 10
3 3 10
4 4 20
5 5 20
6 6 20
7 7 30
8 8 30
9 9 30
select row_number() over(order by name) as rn,name,age from person;
select row_number() over(order by name) as rn,name,age from person;