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|
+--------+------+-----+

我想要的是这样的名字：

+--------+------+---------+
|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  |
+--------+------+---------+

我试过：

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

您可以使用中的罗马数字格式说明符RN来指定字符

SELECT
    level as n,
    TO_CHAR(level,'RN') as roman
FROM
    dual
CONNECT BY
    level <= 10;


         N ROMAN          
---------- ---------------
         1               I
         2              II
         3             III
         4              IV
         5               V
         6              VI
         7             VII
         8            VIII
         9              IX
        10               X

10 rows selected. 

因此，在APC的查询中，您可以执行to_char（rn，'RN'）||name as name

Answer 2

ROWNUM只是结果集中的行数。您实际需要的是每个等级中name的数目。所以这意味着你需要一个像row_number（）这样的分析函数：

with cte as (
    select school, rating, name, 
           row_number() over (partition by  school, rating order by name) as rn       
    from mytable
)
select cte.school, 
       cte.rating,
       case when cte.rn = 1 then 'i-' || name
            when cte.rn = 2 then 'ii-' || name
            when cte.rn = 3 then 'iii-' || name
            when cte.rn = 4 then 'iv-' || name
            when cte.rn = 5 then 'v-' || name
        end Name
from cte
where cte.school = 'SchoolN'
;

这是生成所需结果集所需对代码进行的最小更改。我建议您也加入@KaushikNayak建议，并部署rn格式掩码，因为这将使您的代码更加紧凑，不那么脆弱：

...
select cte.school, 
       cte.rating,
       to_char(cte.rn, 'rn')|| '-' || name as Name
from cte
...

通过小写的格式掩码（'rn'），得到小写的输出：i，ii等。

Answer 3

您可以使用字符转换功能（到字符）的fmRM格式模型选项使用densite\u rank（）：

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小提琴演示