行转列和分页查询实验

测试表：

SQL> select * from t;
 
ID         NAME       SUBJECT         GRADE
---------- ---------- ---------- ----------
12         zorro      英语               75
11         pg         英语               90
10         blues      英语      
9          seker      英语               60
8          zorro      数学               80
7          pg         数学               80
6          blues      数学               90
5          seker      数学               75
4          zorro      语文               70
3          pg         语文      
2          blues      语文               60
1          seker      语文               65
 
12 rows selected

 

1.行转列

SQL> select name,
  2  sum(decode(subject,'语文',grade,null)) as "语文",
  3  sum(decode(subject,'数学',grade,null)) as "数学",
  4  sum(decode(subject,'英语',grade,null)) as "英语"
  5  from t
  6  group by name
  7  order by name;
 
NAME               语文         数学         英语
---------- ---------- ---------- ----------
blues              60         90
pg                            80         90
seker              65         75         60
zorro              70         80         75

 

要求：

60分以上的成绩显示为A，60分的显示为B，60分一下的显示为C

首先使用decode(sign())转换分数等级

SQL> select name,subject,decode(sign(grade-60),1,'A',0,'B',-1,'C') "等级" from t;
 
NAME       SUBJECT    等级
---------- ---------- ------
zorro      英语       A
pg         英语       A
blues      英语      
seker      英语       B
zorro      数学       A
pg         数学       A
blues      数学       A
seker      数学       A
zorro      语文       A
pg         语文      
blues      语文       B
seker      语文       A
 
12 rows selected
 

然后行转列：

SQL> select name,
  2  max(decode(subject,'语文',rank,null)) "语文",
  3  max(decode(subject,'数学',rank,null)) "数学",
  4  max(decode(subject,'英语',rank,null)) "英语"
  5  from(select name,subject,decode(sign(grade-60),1,'A',0,'B',-1,'C') rank from t)
  6  group by name;
 
NAME       语文   数学   英语
---------- ------ ------ ------
pg                A      A
blues      B      A     
zorro      A      A      A
seker      A      A      B

 

查询每门课的前三名同学的姓名，分数

SQL> select subject,
  2  max(decode(rank,1,name)) 第一名,
  3  max(decode(rank,1,grade)) 第一名分数,
  4  max(decode(rank,2,name)) 第二名,
  5  max(decode(rank,2,grade)) 第二名分数,
  6  max(decode(rank,3,name)) 第三名,
  7  max(decode(rank,3,grade)) 第三名分数
  8  from(select name,subject,grade,row_number() over(partition by subject order by grade desc) rank from t where grade is not null)
  9  group by subject;
 
SUBJECT    第一名               第一名分数 第二名               第二名分数 第三名               第三名分数
---------- ---------- --------------- ---------- --------------- ---------- ---------------
数学       blues                   90 pg                      80 zorro                   80
英语       pg                      90 zorro                   75 seker                   60
语文       zorro                   70 seker                   65 blues                   60
 

2.分页查询

查询第6条到第10记录

SQL> select * from(select t.*,rownum rn from(select * from t)t where rownum<11) where rn>5;
 
ID         NAME       SUBJECT         GRADE         RN
---------- ---------- ---------- ---------- ----------
7          pg         数学               80          6
6          blues      数学               90          7
5          seker      数学               75          8
4          zorro      语文               70          9
3          pg         语文                          10

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26425571/viewspace-735426/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26425571/viewspace-735426/