SQL> select sal,job from emp; SAL JOB
---------- ---------
800 CLERK
1600 SALESMAN
1250 SALESMAN
2975 MANAGER
1250 SALESMAN
2850 MANAGER
2450 MANAGER
3000 ANALYST
5000 PRESIDENT
1500 SALESMAN
1100 CLERK SAL JOB
---------- ---------
950 CLERK
3000 ANALYST
1300 CLERK已选择14行。
SQL> select job,sal,rank() over(partition by job order by sal) from emp;JOB SAL RANK()OVER(PARTITIONBYJOBORDERBYSAL)
--------- ---------- ------------------------------------
ANALYST 3000 1
ANALYST 3000 1
CLERK 800 1
CLERK 950 2
CLERK 1100 3
CLERK 1300 4
MANAGER 2450 1
MANAGER 2850 2
MANAGER 2975 3
PRESIDENT 5000 1
SALESMAN 1250 1JOB SAL RANK()OVER(PARTITIONBYJOBORDERBYSAL)
--------- ---------- ------------------------------------
SALESMAN 1250 1
SALESMAN 1500 3
SALESMAN 1600 4已选择14行。SQL>
---------- ---------
800 CLERK
1600 SALESMAN
1250 SALESMAN
2975 MANAGER
1250 SALESMAN
2850 MANAGER
2450 MANAGER
3000 ANALYST
5000 PRESIDENT
1500 SALESMAN
1100 CLERK SAL JOB
---------- ---------
950 CLERK
3000 ANALYST
1300 CLERK已选择14行。
SQL> select job,sal,rank() over(partition by job order by sal) from emp;JOB SAL RANK()OVER(PARTITIONBYJOBORDERBYSAL)
--------- ---------- ------------------------------------
ANALYST 3000 1
ANALYST 3000 1
CLERK 800 1
CLERK 950 2
CLERK 1100 3
CLERK 1300 4
MANAGER 2450 1
MANAGER 2850 2
MANAGER 2975 3
PRESIDENT 5000 1
SALESMAN 1250 1JOB SAL RANK()OVER(PARTITIONBYJOBORDERBYSAL)
--------- ---------- ------------------------------------
SALESMAN 1250 1
SALESMAN 1500 3
SALESMAN 1600 4已选择14行。SQL>
解决方案 »
- 救助:ora-01427:单行子查询返回多个行
- ORACLE查询表中某一个字段相同的记录(大于一个),谁能给优化一下
- SQL语句问题,急,在线等
- 一年有365天 每天都有数据 我只想显示 每隔十天的数据
- 一家外资的一道sql面试题.....
- ORACLE常用网址,欢迎补充!
- 存储过程 :提示错误:行号=1 列号=30 错误文本=PLS-00103出现”create"在需要下列之一时: (; is with authid as cluster
- 更新一个字段,使其唯一
- 在Oracle中如何用SQL取出前几条纪录?(在线等待)
- oracle中的自定义函数怎么修改?
- ******这条SQL为什么不能执行,解决立即给分*****
- 关于oracle与VB的问题
COLA COLB COUNT(COLA)OVER(PARTITIONBYCOLAORDERBYCOLB)
---------- ---------- -------------------------------------------
a d 1
a g 2
a q 3
b f 1
b gh 2
b rt 3
c er 1
c rt 2已选择8行。
---------- ----------
A aa
A bb
B b
A b
B ccSQL> select maxid-id+1,tt.aa,tt.bb from (
2 select max(id) maxid,aa from (
3 select rownum id,aa,bb from (
4 select * from t order by aa)) group by aa
5 ) t,
6 (select rownum id,aa,bb from (
7 select * from t order by aa)) tt
8 where tt.aa=t.aa(+);MAXID-ID+1 AA BB
---------- ---------- ----------
3 A aa
2 A bb
1 A b
2 B b
1 B ccSQL>
感谢 bzszp(www.bzszp.533.net)的关心:开拓了思路.
实际答案:
select tt.cola, tt.colb, id-minid+1
from (select cola,min(id) minid
from (select rownum id, cola, colb
from (select * from t2_fenzu order by cola))
group by cola) t,
(select rownum id, cola, colb
from (select * from t2_fenzu order by cola)) tt
where t.cola = tt.cola