将与多个范围匹配的数字列表转换为值列表的 SQL 查询 [英] SQL query to translate a list of numbers matched against several ranges, to a list of values

问题描述

我需要将特定范围内的数字列表转换为按优先级列排序的值列表.该表具有以下值:

I need to convert a list of numbers that fall within certain ranges into a list of values, ordered by a priority column. The table has the following values:

| YEAR | R_MIN |  R_MAX | VAL | PRIO |
------------------------------------
  2010   18000    90100   52    6
  2010  240000   240099   82    3
  2010  250000   259999   50    5
  2010  260000   260010   92    1
  2010  330000   330010   73    4
  2010  330011   370020   50    5
  2010  380000   380050   84    2

不同年份的范围会有所不同.一年内的范围永远不会重叠.

The ranges will be different for different years. The ranges within one year will never overlap.

输入将是年份和可能落在这些范围内的数字列表.输入数字的列表会很小，1 到 10 个数字.输入数字示例:

The input will be a year and a list of numbers that might fall within one these ranges. The list of input number will be small, 1 to 10 numbers. Example of input numbers:

(20000, 240004, 375000, 255000)

有了这个输入，我想得到一个按优先级列或单个值排序的列表:

With that input I would like to get a list ordered by the priority column, or a single value:

82
50
52

我在这里唯一感兴趣的值是 82，所以 UNIQUE 和 MAX_RESULTS=1 就可以了.可以轻松地对每个数字进行一次查询，然后在 Java 代码中对其进行排序，但我更愿意在单个 SQL 查询中进行.

The only value I'm interested in here is 82, so UNIQUE and MAX_RESULTS=1 would do. It can easily be done with one query per number, and then sorting it in the Java code, but I would prefer to do it in a single SQL query.

在 Oracle 数据库中运行的什么 SQL 查询会给我想要的结果?

What SQL query, to be run in an Oracle database, would give me the desired result?

(注意，这不是关于 拆分输入字符串，就是将值列表中的每个值与不同列中定义的范围匹配.)

(Note, this is not about splitting an input string, it's about matching each value in a list of values to ranges defined in different columns.)

推荐答案

我猜您想将这组数字作为字符串传递并拆分为单独的数字.这比您想象的要难，因为 Oracle 没有内置分词器.很奇怪吧?

I am guessing you want to pass that set of numbers as a string and split into into individual numbers. This is harder than you might think, because Oracle doesn't come with a built-in tokenizer. Weird, huh?

有许多 PL/SQL 标记器解决方案围绕 Das Interwas.我正在使用 Anup Pani 的实现的变体，它使用 Regex(因此只有 Oracle 10g 或更高版本).我的变体返回一个我已声明为 SQL 类型的数字数组:

There are a number of PL/SQL tokenizer solutions knocking around Das Interwabs. I am using a variant of Anup Pani's implementation, which uses Regex (hence only Oracle 10g or higher). My variant returns an array of numbers which I have declared as a SQL type:

SQL> create or replace type numbers as table of number
  2  /

Type created.

SQL>

这意味着我可以将它用作 SELECT 语句中 TABLE() 函数的输入:

This means I can use it as an input to a TABLE() function in a SELECT statement:

SQL> select * from table (str_to_number_tokens('20000, 240004, 375000, 255000'))
  2  /

COLUMN_VALUE
------------
       20000
      240004
      375000
      255000

SQL>

这意味着我可以将您的一串数字转换成一个表格，我可以在查询中加入该表格，如下所示:

This means I can turn your string of numbers into a table which I can join to in a query, like this:

SQL> select val
  2  from t23
  3       , ( select column_value as i_no
  4           from table (str_to_number_tokens('20000, 240004, 375000, 255000')) ) sq
  5  where t23.year = 2010
  6  and   sq.i_no between t23.r_min and t23.r_max
  7  order by t23.priority
  8  /

       VAL
----------
        82
        50
        52

SQL>

这篇关于将与多个范围匹配的数字列表转换为值列表的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！