SQL> select owner, pct_free 2 from all_tables a 3 where a.owner='SYS' and rownum<10 order by a.pct_free 4 desc 5 /OWNER PCT_FREE ------------------------------------------------------------ ---------- SYS 10 SYS 10 SYS 10 SYS 10 SYS 10 SYS 10 SYS 10 SYS 10 SYS 10已选择9行。SQL>
select * from (select class_type,name,score, rank() over(partition by class_type order by score desc) mc from student) where mc<=10;
STUDENT NAME CLASS_TYPE SCORE 张三 A1 79 李四 A1 82 王五 A2 65 陈七 A3 73 select * from ( select student.*,row_number() over (partition by class_type order by score desc) rn from student ) where rn <= 10;
你就不能分个组,用group by CLASS_TYPE
select class_type,name,rk from (select class_type,name,rank() over(partition by class_type order by score desc) rk from student) where rk<=10 group by class_type,name
select class_type,name,rk from (select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc) where rk<=10 group by class_type,name
select class_type,name,rk from (select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc) where rk<=10 group by class_type,name
DECLARE TYPE STUDENTLIST IS VARRVAY(15) OF VARCHAR2(16); STUDENTS STUDENTLIST:=STUDENTLIST('A1','A2','A3','A4','A5','A6','A7','A8'); NAMES VARCHAR2(8); CLASS_TYPES VARCHAR2(6); SCORE NUMBER(3); BEGIN FOR I IN 1..8 LOOP FOR J IN 1..10 LOOP select * INTO NAMES,CLASS_TYPES,SCORE from student s where s.class_type=STUDENTS(I) and rownum<=10 order by score desc; DBMS_OUTPUT.PUT_LINE(NAMES,CLASS_TYPES,SCORE); END LOOP; END LOOP; END
请问over命令如何运用啊,各位大侠
beckhambobo(beckham)大侠,解释一下吧,我是初学者对(select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc) 看不懂啊,谢谢..
beckhambobo(beckham) 帅 分析函数都用的得心应手
赞同beckhambobo(beckham),用分析函数,不过要在8。1。6版本以上才能用 select class_type,name,rk from (select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc) where rk<=10 group by class_type,name
我试了,rank()函数的确可以解决问题。谢谢大家了。 另外,哪里有关于分析函数的资料呢?
分析函数资料: http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#81409RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers. 以下是一个例子,可以参考: SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk FROM emp; DEPTNO ENAME SAL COMM RK ---------- ---------- ---------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 1 20 JONES 2975 3 20 ADAMS 1100 4 20 SMITH 800 5 30 BLAKE 2850 1 30 ALLEN 1600 300 2 30 TURNER 1500 0 3 30 WARD 1250 500 4 30 MARTIN 1250 1400 5 30 JAMES 950 6
to:beckhambobo(beckham):这个语句我觉得有问题: select class_type,name,rk from (select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc) where rk<=10 group by class_type,name 后面加一个group by class_type,name没有道理,要么全加 group by class_type,name,rk 要么不加,其实加了也没多大意义.
2 from all_tables a
3 where a.owner='SYS' and rownum<10 order by a.pct_free
4 desc
5 /OWNER PCT_FREE
------------------------------------------------------------ ----------
SYS 10
SYS 10
SYS 10
SYS 10
SYS 10
SYS 10
SYS 10
SYS 10
SYS 10已选择9行。SQL>
rank() over(partition by class_type order by score desc) mc from
student) where mc<=10;
NAME CLASS_TYPE SCORE
张三 A1 79
李四 A1 82
王五 A2 65
陈七 A3 73
select * from (
select student.*,row_number() over (partition by class_type order by score desc) rn
from student
)
where rn <= 10;
(select class_type,name,rank() over(partition by class_type order by score desc) rk from student)
where rk<=10 group by class_type,name
(select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc)
where rk<=10 group by class_type,name
(select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc)
where rk<=10 group by class_type,name
TYPE STUDENTLIST IS VARRVAY(15) OF VARCHAR2(16);
STUDENTS STUDENTLIST:=STUDENTLIST('A1','A2','A3','A4','A5','A6','A7','A8');
NAMES VARCHAR2(8);
CLASS_TYPES VARCHAR2(6);
SCORE NUMBER(3);
BEGIN
FOR I IN 1..8 LOOP
FOR J IN 1..10 LOOP
select * INTO NAMES,CLASS_TYPES,SCORE
from student s
where s.class_type=STUDENTS(I) and rownum<=10
order by score desc;
DBMS_OUTPUT.PUT_LINE(NAMES,CLASS_TYPES,SCORE);
END LOOP;
END LOOP;
END
看不懂啊,谢谢..
select class_type,name,rk from
(select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc)
where rk<=10 group by class_type,name
另外,哪里有关于分析函数的资料呢?
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#81409RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.
以下是一个例子,可以参考:
SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk
FROM emp; DEPTNO ENAME SAL COMM RK
---------- ---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 3
20 ADAMS 1100 4
20 SMITH 800 5
30 BLAKE 2850 1
30 ALLEN 1600 300 2
30 TURNER 1500 0 3
30 WARD 1250 500 4
30 MARTIN 1250 1400 5
30 JAMES 950 6
select class_type,name,rk from
(select class_type,name,rank() over(partition by class_type order by score desc) rk from student order by score desc)
where rk<=10 group by class_type,name
后面加一个group by class_type,name没有道理,要么全加 group by class_type,name,rk
要么不加,其实加了也没多大意义.