sql - Oracle SQL 案例

Question

我的查询:

select school, rating, name
from mytable
where school = 'SchoolN';

结果:

+--------+------+-----+|School  |Rating|Name |+--------+------+-----+|SchoolN |  A   |Name1||SchoolN |  B   |Name2||SchoolN |  A   |Name3||SchoolN |  A   |Name4||SchoolN |  A   |Name5||SchoolN |  B   |Name6||SchoolN |  B   |Name7||SchoolN |  B   |Name8||SchoolN |  B   |Name9|+--------+------+-----+

What I wanted is to make name like this:

+--------+------+---------+|School  |Rating|Name     |+--------+------+---------+|SchoolN |  A   |i-Name1  ||SchoolN |  B   |i-Name2  ||SchoolN |  A   |ii-Name3 ||SchoolN |  A   |iii-Name4||SchoolN |  A   |iv-Name5 ||SchoolN |  B   |ii-Name6 ||SchoolN |  B   |iii-Name7||SchoolN |  B   |iv-Name8 ||SchoolN |  B   |v-Name9  |+--------+------+---------+

I tried:

select school, rating,
case when rownum = 1 then 'i-' || name
     when rownum = 2 then 'ii-' || name
     when rownum = 3 then 'iii-' || name
     when rownum = 4 then 'iv-' || name
     when rownum = 5 then 'v-' || name
end Name
from mytable
where school = 'SchoolN';

但是结果是错误的:

+--------+------+---------+|School  |Rating|Name     |+--------+------+---------+|SchoolN |  A   |i-Name1  ||SchoolN |  B   |ii-Name2 ||SchoolN |  A   |iii-Name3||SchoolN |  A   |iv-Name4 ||SchoolN |  A   |v-Name5  ||SchoolN |  B   |Name6    ||SchoolN |  B   |Name7    | |SchoolN |  B   |Name8    ||SchoolN |  B   |Name9    |+--------+------+---------+

Answer 1

你可以使用 dense_rank() 和字符转换函数的 fmRM 格式模型选项(to_char):

select t.school,
       t.rating,
       to_char(dense_rank() over(partition by rating order by name), 'fmrm') 
       || '-' ||t.name name
  from mytable t
 order by t.name;

SQL Fiddle Demo