将值列表与表进行比较 [英] Comparing list of values against table

问题描述

我试图找到这个问题的解决方案一段时间，但没有成功，所以任何帮助将不胜感激。 ID列表需要与表进行比较，并找出存在哪些记录（及其值之一）以及哪些记录不存在。
有文本格式的ID列表：

  100，
 200，
 300 
  

一个DB表格：

  ID（PK）value01 value02 value03 ..... 
 -------------------------- ------------ 
 100 Ann 
 102 Bob 
 300 John 
 304 Marry 
 400 Jane 
  

，输出我需要的是：

  100 Ann 
 200缺失或空白或任何指示
 300 John 
  

解决方案是创建表和连接，但我只有读访问（DB是封闭的供应商产品，我只是一个用户）。写一个PL / SQL函数似乎很复杂，因为表有200多列和100k +记录，我没有运气创建动态记录数组。此外，要检查的ID列表包含数百个ID，我需要定期这样做，所以任何解决方案，其中每个ID必须在单独的代码行更改将不是非常有用。
数据库是Oracle 10g。

解决方案

有很多内置的公共集合类型。你可以像下面这样利用其中的一个：

 用ids as（select / * + cardinality（a，1）* / column_value id 
从表（UTL_NLA_ARRAY_INT（100，200，300））a 
）
 select ids.id，当m.id为null时，'**无匹配**'else m .value end value 
 from ids 
 left outer join my_table m 
 on m.id = ids.id; 
  

查看数据库上的公共类型列表，运行：

选择所有者，type_name，coll_type，elem_type_name，upper_bound，precision，从all_coll_types缩放
其中elem_type_name在（'FLOAT'，'INTEGER'，'NUMBER '，'DOUBLE PRECISION'）


提示

  / * +基数（a，1）* / 
  

只是用来告诉oracle在我们的数组中有多少个元素（如果没有指定，默认会有8k个元素的假设）。只是设置为相当准确的数字。

I tried to find solution for this problem for some time but without success so any help would be much appreciated. List of IDs needs to be compared against a table and find out which records exist (and one of their values) and which are non existent.
There is a list of IDs, in text format:

100,
200,
300 

a DB table:

ID(PK)   value01 value02 value03 .....
--------------------------------------
100       Ann
102       Bob
300       John
304       Marry
400       Jane

and output I need is:

100 Ann
200 missing or empty or whatever indication
300 John

Obvious solution is to create table and join but I have only read access (DB is closed vendor product, I'm just a user). Writing a PL/SQL function also seems complicated because table has 200+ columns and 100k+ records and I had no luck with creating dynamic array of records. Also, list of IDs to be checked contains hundreds of IDs and I need to do this periodically so any solution where each ID has to be changed in separate line of code wouldn't be very useful. Database is Oracle 10g.

解决方案

there are many built in public collection types. you can leverage one of them like this:

with ids as (select /*+ cardinality(a, 1) */ column_value id
               from table(UTL_NLA_ARRAY_INT(100, 200, 300)) a
            )
select ids.id, case when m.id is null then '**NO MATCH**' else m.value end value
  from ids
         left outer join my_table m
                     on m.id = ids.id;

to see a list of public types on your DB, run :

select owner, type_name, coll_type, elem_type_name, upper_bound, precision, scale from all_coll_types
 where elem_type_name in ('FLOAT', 'INTEGER', 'NUMBER', 'DOUBLE PRECISION')

the hint

/*+ cardinality(a, 1) */

is just used to tell oracle how many elements are in our array (if not specified, the default will be an assumption of 8k elements). just set to a reasonably accurate number.

这篇关于将值列表与表进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！