plsql排序集

想限制排序方式可以用分析函數(shù) 求出各科前3名 SELECT STUDENT_ID,NAME,COURSE,SCORE,RANK FROM (SELECT STUDENT_ID,NAME,COURSE,SCORE, ROW_NUMBER() OVER (PARTITION BY COURSE ORDER BY SCORE DESC NULLS LAST) RANK FROM SCORE) WHERE RANK <= 3; STUDENT_ID NAME COURSE SCORE RANK

2 喜羊羊 數(shù)學(xué) 100 1
3 機(jī)器貓 數(shù)學(xué) 80 2
4 藍(lán)精靈 數(shù)學(xué) 80 3
1 灰太狼 語文 5 1
Oracle分析函數(shù) 朱元中
2013-01-17 page 21
ROW_NUMBER()
SELECT STUDENT_ID,NAME,COURSE,SCORE,
DENSE_RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 數(shù)學(xué) 100 1
3 機(jī)器貓 數(shù)學(xué) 80 2
4 藍(lán)精靈 數(shù)學(xué) 80 2
5 Darwin 數(shù)學(xué) 50 3
1 灰太狼 數(shù)學(xué) 4
1 灰太狼 語文 5 1
SELECT STUDENT_ID,NAME,COURSE,SCORE,RANK
FROM (SELECT STUDENT_ID,NAME,COURSE,SCORE,
DENSE_RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE)
WHERE RANK <= 3;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 數(shù)學(xué) 100 1
3 機(jī)器貓 數(shù)學(xué) 80 2
4 藍(lán)精靈 數(shù)學(xué) 80 2
5 Darwin 數(shù)學(xué) 50 3
1 灰太狼 語文 5 1
SELECT STUDENT_ID,NAME,COURSE,SCORE,
RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 數(shù)學(xué) 100 1
3 機(jī)器貓 數(shù)學(xué) 80 2
4 藍(lán)精靈 數(shù)學(xué) 80 2
5 Darwin 數(shù)學(xué) 50 4
1 灰太狼 數(shù)學(xué) 5
1 灰太狼 語文 5 1
SELECT STUDENT_ID,NAME,COURSE,SCORE,RANK
FROM (SELECT STUDENT_ID,NAME,COURSE,SCORE,
RANK() OVER (PARTITION BY COURSE
ORDER BY SCORE DESC NULLS LAST) RANK
FROM SCORE)
WHERE RANK <= 3;
STUDENT_ID NAME COURSE SCORE RANK
---------- -------- ------ ---------- -----
2 喜羊羊 數(shù)學(xué) 100 1
3 機(jī)器貓 數(shù)學(xué) 80 2
4 藍(lán)精靈 數(shù)學(xué) 80 2
1 灰太狼 語文 5 1