decode和instr的用法以及 row_number() over语法

最新推荐文章于 2022-06-07 18:30:40 发布

原创最新推荐文章于 2022-06-07 18:30:40 发布 · 2.6k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#string #date #2010

DataBase 专栏收录该内容

18 篇文章

订阅专栏

本文详细介绍了一种复杂的SQL查询技巧，包括使用row_number()进行结果集分区排序、DECODE函数进行条件判断以及instr函数进行字符串定位等高级用法，旨在帮助读者理解和掌握这些SQL技巧。

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