oracle某个数据表把不同字段排序

Question

各位大侠：
     小弟有一问题请教：
      有一数据表 create table aa(id int not null,certiNo varchar(20) not null,banji varchar(10),shuxu double,yuwen,double,dilie double)
      想查询某班级下所有学生各个学科的排名，结果如下：
    学生     班级      语文 排名   数学  排名   地理 排名
请高手们指点，存储过程，SQL都可以。
      

Answer 1

select * from aa order by shuxue desc;
select * from aa order by yuwen  desc;
select * from aa order by dilie  desc;不知道楼主是不是这意思

Answer 2

select t.id, t.banji, t.shuxu, t1.shuxu_id, t.yuwen, t2.yuwen_id, t.dilie , t3.dilie_id 
from aa t,
(select id , rownum shuxu_id from(
select id from aa order by shuxu asc))t1
,
(select id , rownum yuwen_id from(
select id from aa order by yuwen asc ))t2
,
(select id , rownum dilie_id from(
select id from aa order by dilie asc))t3
where t.id=t1.id and t.id=t2.id and t.id=t3.id

Answer 3

with test as (select 'aa' cname,98 yuwen,80 shuxue ,70 yingyu from dual
union all select 'bb' ,80,60,30 from dual
union all select 'cc' ,70,90,80 from dual)
select cname,yuwen,shuxue,yingyu,
dense_rank()over(order by YUWEN desc nulls last) a,
dense_rank()over(order by shuxue desc nulls last) b,
dense_rank()over(order by yingyu desc nulls last) c
 from test t

Answer 4

aa 98 80 70 1 2 2
bb 80 60 30 2 3 3
cc 70 90 80 3 1 1

Answer 5

dense_rank()
rank()
..
自己选！

Answer 6

其实最终也是那么搞的，只是oracle帮你做了！

Answer 7

with t as(
select 1 学号,30 语文,45 数学,76 英语 from dual
union all
select 2,39,73,94 from dual
union all
select 3,64,92,78 from dual
union all
select 4,87,98,84 from dual
union all
select 5,67,67,76 from dual
union all
select 6,78,87,89 from dual
union all
select 7,45,45,78 from dual
union all
select 8,78,68,23 from dual
union all
select 9,76,56,88 from dual
union all
select 10,67,88,90 from dual
)select 
t.学号,t.语文,dense_rank() over(order by 语文) 语文排名,
t.数学,dense_rank() over(order by 数学) 数学排名,
t.英语,dense_rank() over(order by 英语) 英语排名 from t
order by 学号        学号         语文       语文排名       数学       数学排名     英语      英语排名
---------- ---------- ---------- --------- ---------- ---------- ----------
         1         30          1         45          1         76          2
         2         39          2         73          5         94          8
         3         64          4         92          8         78          3
         4         87          8         98          9         84          4
         5         67          5         67          3         76          2
         6         78          7         87          6         89          6
         7         45          3         45          1         78          3
         8         78          7         68          4         23          1
         9         76          6         56          2         88          5
        10         67          5         88          7         90          7

Answer 8

with t as(
select 1 学号,30 语文,45 数学,76 英语 from dual
union all
select 2,39,73,94 from dual
union all
select 3,64,92,78 from dual
union all
select 4,87,98,84 from dual
union all
select 5,67,67,76 from dual
union all
select 6,78,87,89 from dual
union all
select 7,45,45,78 from dual
union all
select 8,78,68,23 from dual
union all
select 9,76,56,88 from dual
union all
select 10,67,88,90 from dual
)select 
t.学号,t.语文,dense_rank() over(order by 语文 desc) 语文排名,
t.数学,dense_rank() over(order by 数学 desc) 数学排名,
t.英语,dense_rank() over(order by 英语 desc) 英语排名 from t
order by 学号
        学号         语文       语文排名       数学      数学排名       英语      英语排名
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         30          8         45          9         76          7
         2         39          7         73          5         94          1
         3         64          5         92          2         78          6
         4         87          1         98          1         84          5
         5         67          4         67          7         76          7
         6         78          2         87          4         89          3
         7         45          6         45          9         78          6
         8         78          2         68          6         23          8
         9         76          3         56          8         88          4
        10         67          4         88          3         90          2

Answer 9

with t as(
select 1 学号,30 语文,45 数学,76 英语 from dual
union all
select 2,39,73,94 from dual
union all
select 3,64,92,78 from dual
union all
select 4,87,98,84 from dual
union all
select 5,67,67,76 from dual
union all
select 6,78,87,89 from dual
union all
select 7,45,45,78 from dual
union all
select 8,78,68,23 from dual
union all
select 9,76,56,88 from dual
union all
select 10,67,88,90 from dual
)select 
t.学号,dense_rank() over (order by t.语文+t.数学+t.英语 desc) 班排名,
t.语文,dense_rank() over(order by 语文 desc) 语文排名,
t.数学,dense_rank() over(order by 数学 desc) 数学排名,
t.英语,dense_rank() over(order by 英语 desc) 英语排名 from t
order by 学号
        学号        班排名         语文       语文排名         数学       数学排名         英语       英语排名
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         10         30          8         45          9         76          7
         2          7         39          7         73          5         94          1
         3          4         64          5         92          2         78          6
         4          1         87          1         98          1         84          5
         5          6         67          4         67          7         76          7
         6          2         78          2         87          4         89          3
         7          9         45          6         45          9         78          6
         8          8         78          2         68          6         23          8
         9          5         76          3         56          8         88          4
        10          3         67          4         88          3         90          2

Answer 10

如果查询的字段级排名都是动态，动态拼出来的SQL语句报ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小错误，具体啥怎么弄成的了，变量我都定了varchar2(4000);

Answer 11

写的挺好，学习了一下rank、dense_rank。create table course(
   name varchar2(20),
   score integer
);insert into course values('语文',98);
insert into course values('数学',53);
insert into course values('语文',98);
insert into course values('数学',69);
insert into course values('数学',78);
insert into course values('语文',88);
insert into course values('数学',49);
insert into course values('数学',81);
insert into course values('语文',97);select * from course;
NAME                                                   SCORE
-------------------- ---------------------------------------
语文                                                      98
数学                                                      53
语文                                                      98
数学                                                      69
数学                                                      78
语文                                                      88
数学                                                      49
数学                                                      81
语文                                                      97
[code=SQL]
select * from (select rank() over(partition by name order by score desc) rk,c.* from course c) t where t.rk<=3; 
 
        RK NAME                                                   SCORE
---------- -------------------- ---------------------------------------
         1 数学                                                      81
         2 数学                                                      78
         3 数学                                                      69
         1 语文                                                      98
         1 语文                                                      98
         3 语文                                                      97
 
6 rows selectedselect * from (select dense_rank() over(partition by name order by score desc) rk,c.* from course c) t where t.rk<=3; 
 
        RK NAME                                                   SCORE
---------- -------------------- ---------------------------------------
         1 数学                                                      81
         2 数学                                                      78
         3 数学                                                      69
         1 语文                                                      98
         1 语文                                                      98
         2 语文                                                      97
         3 语文                                                      88
 
7 rows selected
[/code]