elect emp_id,emp_name ,rank() over(order by emp_name)axh from
(select emp_id,emp_name,rank() over(order by emp_name)xh from table t)
where xh < =5
(select emp_id,emp_name,rank() over(order by emp_name)xh from table t)
where xh < =5
解决方案 »
- 9i和10g区别大吗
- oracle impdp问题!
- 往主键索引表插入上万条数据的效率问题
- form builder中怎样打开LIB的源代码并进行修改?修改完之后需要重新编译吗?先谢谢了。
- 一个职员有多个工号,职务时如何在一行显示,用一个sql实现
- 有免费版本的oracle吗?怎么找不到?
- SalaryH number:=&SalaryH;报错???
- 替换clob字段中的某几个字符的sql或储存过程该怎么写?
- 哪位能帮我写出这个SQL??
- 旧的问题,oracle 8i在P4+WIN98 下安装的问题!!!!(各位大虾千万帮忙,在线等候,答对的分)
- 关于DEFINE_COLUMN的问题
- 只想在备份数据库中恢复其中的一个表,请问能行吗?如果行,怎样来处理!请赐教
(select rank() over(order by emp_name)xh, emp_id, emp_name from table t)
where xh < =5
(select rank() over(order by sal)xh, emp_name, sal from table t)
where xh < =5
2 (select rank() over(order by sal) xh, emp_name, sal from table t)
3 where xh < =5;
(select rank() over(order by sal) xh, emp_name, sal from table t)
*
ERROR 位于第 2 行:
ORA-00906: 缺少左括号
(select f1,f2,rank() over(order by f2)xh from aaa t)
where xh < =5我是这样写的,再plsql里面运行过,没有问题,你把字段名改一下
SQL> select emp_id,emp_name,sal,rank() over(order by sal)axh from
2 (select emp_id,emp_name,sal,rank() over(order by sal)xh from employee t)
3 where xh < =5; EMP_ID EMP_NAME SAL AXH
---------- -------------------- ---------- ----------
1 关羽 4632 1
2 张飞 4632 1
3 刘备 4932 3
5 黄忠 4932 3
8 姜维 4932 3
declare
cursor cur is select * from employee order by sal desc;
emprow employee%rowtype;
cnt number:=0;
temp number:=0;
f number:=0;
n number:=0;
begin
open cur;
loop
fetch cur into emprow;
if cur%notfound then
dbms_output.new_line();
exit;
end if;
if emprow.sal!=temp then
if f=1 then
for i in 1..n loop
dbms_output.new_line();
if cnt+i>5 then
dbms_output.new_line();
exit;
end if;
dbms_output.put((cnt+i) || '.');
end loop;
cnt:=cnt+n;
n:=0;
f:=0;
end if;
cnt:=cnt+1;
if cnt>5 then
dbms_output.new_line();
exit;
end if;
dbms_output.new_line();
dbms_output.put(cnt || '.');
dbms_output.put(' ');
dbms_output.put(emprow.emp_name);
dbms_output.put(',');
dbms_output.put(emprow.sal);
temp:=emprow.sal;
else
dbms_output.put(' ');
dbms_output.put(emprow.emp_name);
dbms_output.put(',');
dbms_output.put(emprow.sal);
f:=1;
n:=n+1;
end if;
end loop;
close cur;
end;
/
你把那个排序改为降序排列不就可以了??
select emp_id,emp_name,sal,rank() over(order by sal desc)axh from
2 (select emp_id,emp_name,sal,rank() over(order by sal desc)xh from employee t)
3 where xh < =5;