select * from 表,请问如何给结果编号。就是在查询结果的前面加上1、2、3、4。。这样的数字。有100条的话就是1~100。
解决方案 »
- 帮忙看看这个追踪日志文件的内容意思
- 在装oracle 11g的时候出现 由于以下错误,Enterprise Manager 配置失败
- oracle运行上下文的内存泄露问题
- 下面的过程为什么执行时会出现下列错误
- 求一简单模糊查询存储过程
- 这样写游标能与查询的顺序对得上吗?老是提示文字与格式字符串不匹配,检查过几次,该是日期的就是日期的啦
- 请问一个SQL语句的写法
- 请教ORACLE9I的默认帐户和密码
- 前辈门我安装了ORACLE8.16了但有几个小问题,请指教
- oracle DBConsoleorcl oracle 11g发生服务特定错误: 2
- 谢谢大家对“如果你是面试官,你将问哪些trigger知识……”的回答
- 请教:oralce多表关联条件查询的问题
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
楼上正解!
利用oracle的伪列rownum。
--如果需要排序后再编号,就需要用子查询,如:
select rownum, a.* from (select * from emp t order by t.sal) a;--就不能使用下面的语句了
select rownum, t.* from emp t order by t.sal;
from (
select -60 as sale,3 as cnt from dual union
select 24 as sale,6 as cnt from dual union
select 50 as sale,5 as cnt from dual union
select -20 as sale,2 as cnt from dual union
select 40 as sale,8 as cnt from dual);select ename,sal,deptno,row_number() over (partition by deptno order by sal desc) as sal_order
from (select 'KING' as ename,5000 as sal,10 as deptno from dual union
select 'CLARK' as ename,2450 as sal,10 as deptno from dual union
select 'MILLER' as ename,1300 as sal,10 as deptno from dual union
select 'SCOTT' as ename,3000 as sal,30 as deptno from dual union
select 'FORD' as ename,3000 as sal,20 as deptno from dual union
select 'JONES' as ename,2975 as sal,50 as deptno from dual union
select 'ADAMS' as ename,1100 as sal,30 as deptno from dual union
select 'SMITH' as ename,800 as sal,20 as deptno from dual union
select 'BLAKE' as ename,2850 as sal,10 as deptno from dual);
select deptno,ename,sal from
(select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
from (select 'KING' as ename,5000 as sal,10 as deptno from dual union
select 'CLARK' as ename,2450 as sal,10 as deptno from dual union
select 'MILLER' as ename,1300 as sal,10 as deptno from dual union
select 'SCOTT' as ename,3000 as sal,30 as deptno from dual union
select 'FORD' as ename,3000 as sal,20 as deptno from dual union
select 'JONES' as ename,2975 as sal,50 as deptno from dual union
select 'ADAMS' as ename,1100 as sal,30 as deptno from dual union
select 'SMITH' as ename,800 as sal,20 as deptno from dual union
select 'BLAKE' as ename,2850 as sal,10 as deptno from dual))
where sal_order <2;
select deptno,sal,rank() over (partition by deptno order by sal) as rank_order
from (select 'KING' as ename,5000 as sal,10 as deptno from dual union
select 'CLARK' as ename,1300 as sal,10 as deptno from dual union
select 'MILLER' as ename,1300 as sal,10 as deptno from dual union
select 'SCOTT' as ename,3000 as sal,30 as deptno from dual union
select 'FORD' as ename,3000 as sal,30 as deptno from dual union
select 'JONES' as ename,5975 as sal,30 as deptno from dual union
select 'ADAMS' as ename,1100 as sal,30 as deptno from dual union
select 'SMITH' as ename,800 as sal,20 as deptno from dual union
select 'BLAKE' as ename,2850 as sal,10 as deptno from dual)
order by deptno;
select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order
from (select 'KING' as ename,5000 as sal,10 as deptno from dual union
select 'CLARK' as ename,1300 as sal,10 as deptno from dual union
select 'MILLER' as ename,1300 as sal,10 as deptno from dual union
select 'SCOTT' as ename,3000 as sal,30 as deptno from dual union
select 'FORD' as ename,3000 as sal,30 as deptno from dual union
select 'JONES' as ename,5975 as sal,30 as deptno from dual union
select 'ADAMS' as ename,1100 as sal,30 as deptno from dual union
select 'SMITH' as ename,800 as sal,20 as deptno from dual union
select 'BLAKE' as ename,2850 as sal,10 as deptno from dual)
order by deptno; SELECT ename "Ename", deptno "Deptno", sal "Sal",
SUM(sal) OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL) OVER (PARTITION BY deptno ORDER BY ename) "Dept Total",
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ENAME) "Seq"
FROM (select 'KING' as ename,5000 as sal,10 as deptno from dual union
select 'CLARK' as ename,2450 as sal,10 as deptno from dual union
select 'MILLER' as ename,1300 as sal,10 as deptno from dual union
select 'SCOTT' as ename,3000 as sal,30 as deptno from dual union
select 'FORD' as ename,3000 as sal,20 as deptno from dual union
select 'JONES' as ename,2975 as sal,50 as deptno from dual union
select 'ADAMS' as ename,1100 as sal,30 as deptno from dual union
select 'SMITH' as ename,800 as sal,20 as deptno from dual union
select 'BLAKE' as ename,2850 as sal,10 as deptno from dual)
ORDER BY deptno, ename
from (select ... order by a.riqi desc) b
你使用了order排序,则要再用一个子查询。。