Oracle行转列及row_number() over (partition order by) 的用法

table

      name  subject  point

1    张三    数学    90
2    张三    语文    20
3    张三    地理    60
4    张三    英语    80
5    李四    数学    100
6    李四    英语    90
7    李四    语文    50
8    李四    地理    40

 

--　利用wm_concat列转一行以逗号分开　　
select t.name,wm_concat(t.point) from dwcn_ext.test_score t group by t.name

--有用的列转行
select t1.name,
       sum(case
             when t1.subject = '语文' then
              t1.point
           end) 语文,
       sum(case
             when t1.subject = '数学' then
              t1.point
           end) 数学,
       sum(case
             when t1.subject = '英语' then
              t1.point
           end) 英语,
       sum(case
             when t1.subject = '地理' then
              t1.point
           end) 地理
  from dwcn_ext.test_score t1
 group by t1.name
 
--利用row_numer() over (partition filed order by filed),进行同类排序
select t2.name,
       t2.subject,
       t2.point,
       row_number() over(partition by t2.name order by t2.point) rn
  from dwcn_ext.test_score t2