select operation_type,nename,resource_name,num,rn
from
(
select nename,operation_type,resource_name,num,row_number() over (partition by operation_type order by num desc) rn
from
(
select operation_type,substr(nename,1,decode(instr(nename,':'),0,length(nename),instr(nename,':')-1)) as nename, resource_name,sum(CRITICAL_NUM)+sum(MAJOR_NUM)+sum(MINOR_NUM) as num
from ACTIVE_HISTORY_ST
where operation_type in('GPRS','MMS')
and trunc(st_date ,'MM') = to_date('2010-10','yyyy-mm')
group by operation_type,resource_name,nename
) a
)b
where rn <=20
order by operation_type,rn
row_number() over语法:
row_number() over (partition by col1 order by col2 desc) 别名
作用:返回结果集分区内行的序列号,每个分区的第一行从 1 开始。
表示按照col1分组,在分组内按照col2排序
别名表示每组内部排序后的顺序编号
DECODE的语法:
DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。
instr的语法:
instr(string1,string2[,start_position[,nth_appearane]])
string1:源字符串,要在此字符串中查找
string2:要在string1中查找的字符串
start_position:代表string1的哪个位置开始查找。可选参数,默认为1,若参数为正,表示从左到右开始检索,反之从右到左检索。返回要查找的字符串在源字符串中的开始索引
nth_appearance:代表要查找第几次出现的string2,可选参数,默认为1。不允许为负数
注意:若string2在string1中没有找到,则instr函数返回0