网站描文本链接怎么做,口碑好的家装前十强,网站安全证书过期怎么处理,优化大师班级优化大师首先我们来看一下ROWNUM#xff1a;含义解释#xff1a;1、rownum是oracle为从查询返回的行的编号#xff0c;返回的第一行分配的是1#xff0c;第二行是2#xff0c;依此类推。这是一个伪列#xff0c;可以用于限制查询返回的总行数。2、rownum不能以任何基表的名称作为… 首先我们来看一下ROWNUM 含义解释 1、rownum是oracle为从查询返回的行的编号返回的第一行分配的是1第二行是2依此类推。这是一个伪列可以用于限制查询返回的总行数。 2、rownum不能以任何基表的名称作为前缀。 对于ROWNUM来说通常我们可以使用的比较符是和,不能单独的使用、、等比较运算符其实我们可以这样简单的 理解oracle是找到第一条的记录添加序号1之后才可以知道谁是第二条记录然后添加序号2以此类推。所以对于等于来说是可以有例外的就是 rownum1。 我们来看几个简单的演示 scottDB01 create table demo as select demono,ename,sal,comm,deptno from demo; Table created. scottDB01 select rownum,t.* from demo t; ROWNUM demoNO ENAME SAL COMM DEPTNO ---------- ---------- ---------- ---------- ---------- ---------- 1 7369 SMITH 800 20 2 7499 ALLEN 1600 300 30 3 7521 WARD 1250 500 30 4 7566 JONES 2975 20 5 7654 MARTIN 1250 1400 30 6 7698 BLAKE 2850 30 7 7782 CLARK 2450 10 8 7788 SCOTT 3000 20 9 7839 KING 5000 10 10 7844 TURNER 1500 0 30 11 7876 ADAMS 1100 20 12 7900 JAMES 950 30 13 7902 FORD 3000 20 14 7934 MILLER 1300 10 14 rows selected. scottDB01 select rownum,demono,ename,sal from demo where rownum3; ROWNUM demoNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 2 7499 ALLEN 1600 3 7521 WARD 1250 scottDB01 select rownum,demono,ename,sal from demo where rownum3; ROWNUM demoNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 2 7499 ALLEN 1600 scottDB01 select rownum,demono,ename,sal from demo where rownum1; ROWNUM demoNO ENAME SAL ---------- ---------- ---------- ---------- 1 7369 SMITH 800 scottDB01 select rownum,demono,ename,sal from demo where rownum3; no rows selected scottDB01 select rownum,demono,ename,sal from demo where rownum3; no rows selected 如果我们想要查询结果集中的某一段范围的记录比如5-10条的记录该如何查询呢很多开发人员把这样的需求称为分页 scottDB01 select rownum,demono,ename,sal from demo where rownum between 5 and 10; no rows selected 上面是一个错误的例子我们来看正确的写法这里我们使用到了集合运算符minus(减法运算 scottDB01 select rownum,demono,ename,sal from demo where rownum10 2 minus 3 select rownum,demono,ename,sal from demo where rownum4; ROWNUM demoNO ENAME SAL ---------- ---------- ---------- ---------- 5 7654 MARTIN 1250 6 7698 BLAKE 2850 7 7782 CLARK 2450 8 7788 SCOTT 3000 9 7839 KING 5000 10 7844 TURNER 1500 6 rows selected. 如果我们有这样一个需求找到员工demo表中薪水最高的前三名如何来实现呢在sql server中有标准的top n分析语句不过不要放到oracle里来使用不同的数据库还是有区别的。当然了也许你会认为这个问题有歧义是前三个人呢还是薪水排在最高3位的人因为薪水有可能是相同的在这里我们就找前三个人看下面的语句 scottDB01 select rownum,demono,ename,sal 2 from demo 3 where rownum3 4 order by sal desc; ROWNUM demoNO ENAME SAL ---------- ---------- ---------- ---------- 2 7499 ALLEN 1600 3 7521 WARD 1250 1 7369 SMITH 800 这个语句从表面上来看好像是正确的从demo表里查询数据排序最后利用rownum返回前三个人但是我们看语句的执行结果显然是不正确的。对于oracle的语句我们在执行的时候遵循top-down的顺序或者我们可以说语句按照顺序来执行。 当然也有个别例外 scottDB01 select deptno,sum(sal) from demo 2 group by deptno 3 having sum(sal)10000; DEPTNO SUM(SAL) ---------- ---------- 20 10875 scottDB01 select deptno,sum(sal) from demo 2 having sum(sal)10000 3 group by deptno; DEPTNO SUM(SAL) ---------- ---------- 20 10875 对于前面排名的语句当然是有问题的。当第2行语句执行后拿到表中所有的数据第3行语句紧接着执行就把最前面的三条记录取出来了rownum是对查询结果添加序号这个时候再做排序当然拿到的就是对前面三条记录排序的结果如上所示。 正确的思路应该是先做排序再做条件筛选也就是4行子句要在3行之前运行如何来实现呢在这里oracle借助了子查询用oracle的标准表述叫做内联视图inline view当然整个的sql就是oracle的top N分析语句的写法我们来看例子 scottDB01 select rownum rank,t.* 2 from (select demono,ename,sal from demo order by sal desc) t 3 where rownum3; RANK demoNO ENAME SAL ---------- ---------- ---------- ---------- 1 7839 KING 5000 2 7788 SCOTT 3000 3 7902 FORD 3000 在这里oracle其实对内部子查询做了优化处理我们通常认为简单子查询是内层查询先执行然后传递结果给外层查询然后外层查询再执行。 但是对于这个例子如果demo表数据量很大的话那么内层排序需要花的时间就会非常多。而实际上呢oracle会知道外层查询需要的记录数如本例中是3 oracle在对内层查询排序时并不是对demo表中的14条记录做完全的排序根据算法他只要找到sal最高的3条就可以了其余的11条记录是没必要排序的这就大大的节省了语句的执行时间。 如果想要得到排序后的某段数据我们可以通过嵌套的方法来实现 scottDB01 select t1.* 2 from (select rownum rank,t.* from (select demono,ename,sal from demo order by sal desc) t) t1 3 where rank3 and rank7; RANK demoNO ENAME SAL ---------- ---------- ---------- ---------- 3 7788 SCOTT 3000 4 7566 JONES 2975 5 7698 BLAKE 2850 6 7782 CLARK 2450 7 7499 ALLEN 1600 注在前面两个例子中我们使用到了oracle的top N分析不过都是对整张表或者整个结果集来说的。其实oracle 对于类似的操作提供了一套函数我们称之为分析函数分析函数对于数据做统计和分析是非常有帮助的我们在下面只是举一个简单的小例子如果你感兴趣可以看看 scottDB01 select * from demo; demoNO ENAME SAL COMM DEPTNO ---------- ---------- ---------- ---------- ---------- 7369 SMITH 800 20 7499 ALLEN 1600 300 30 7521 WARD 1250 500 30 7566 JONES 2975 20 7654 MARTIN 1250 1400 30 7698 BLAKE 2850 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7844 TURNER 1500 0 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 7934 MILLER 1300 10 14 rows selected. scottDB01 break on deptno skip 1 scottDB01 select * 2 from (select deptno, 3 ename, 4 sal, 5 dense_rank() over(partition by deptno order by sal desc) dr 6 from demo 7 ) 8 where dr3 9 order by deptno; DEPTNO ENAME SAL DR ---------- ---------- ---------- ---------- 10 KING 5000 1 CLARK 2450 2 MILLER 1300 3 20 SCOTT 3000 1 FORD 3000 1 JONES 2975 2 ADAMS 1100 3 30 BLAKE 2850 1 ALLEN 1600 2 TURNER 1500 3 10 rows selected. scottDB01 select * from ( 2 select deptno, 3 ename, 4 sal, 5 row_number() over(partition by deptno order by sal desc) dr 6 from demo 7 ) 8 where dr3 9 order by deptno; DEPTNO ENAME SAL DR ---------- ---------- ---------- ---------- 10 KING 5000 1 CLARK 2450 2 MILLER 1300 3 20 SCOTT 3000 1 FORD 3000 2 JONES 2975 3 30 BLAKE 2850 1 ALLEN 1600 2 TURNER 1500 3
