在Oracle中查找Varchar值的第N个最小值 [英] Finding Nth Minimum of a Varchar value in Oracle

问题描述

我有一个带有Config_ID，Escalation_Level列的oracle表

I have an oracle table with the columns Config_ID, Escalation_Level

在此表中，Escalation_Level是 Varchar ，其值为'L0'，'L1'，'L2'，'L13'，'L4'，'L3'，'L5 '，'L22'，'L19'，'L41'L98'，'L99 '以Config_ID的混乱顺序排列.

In this table the Escalation_Level is a Varchar with the values 'L0','L1','L2','L13','L4','L3','L5','L22','L19','L41''L98','L99' in jumbled order for a Config_ID.

如何查找Escalation_Level的第N个MIN.由于这是Varchar类型，因此我找不到直接的方法.

How to find the Nth MIN of the Escalation_Level. As this is of Varchar type, I don't find a straight forward way.

请分享您的想法.

关于， 斯里拉姆

推荐答案

如果要查找任何内容的Nth值，则分析函数

If you want to find the Nth value of anything then the analytic function NTH_VALUE() is a good place to start.

假设只希望基于数字部分，则必须替换不是数字的所有内容，为此可以使用

Assuming you want this based on the numeric part only you have to replace everything that is not a number, for which you can use REGEXP_REPLACE()

select regexp_replace(escalation_level, '[^[:digit:]]')
  from my_table

要获得给定CONFIG_ID的第N个值，将是:

To obtain the Nth value for a given CONFIG_ID it would be:

select nth_value(escalation_level, n)
         over ( partition by config_id 
                    order by regexp_replace(escalation_level, '[^[:digit:]]') )
  from my_table

其中n是要返回的值的索引.

where n is the index of the value you want to return.

这篇关于在Oracle中查找Varchar值的第N个最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！