比较对表值的列表(Comparing list of values against table)

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

100,
200,
300 

一个数据库表：

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

输出我需要的是：

100 Ann
200 missing or empty or whatever indication
300 John

显而易见的解决方案是创建表和加入，但我也只能读取（DB是封闭的供应商的产品，我只是一个用户）。 编写PL / SQL函数也似乎复杂，因为表中有200个多列和100K +的记录，我没有运气与创建的记录动态数组。 另外，要检查ID列表包含数百个ID的，我需要做定期所以每个ID在单独的代码行改变不会是非常有用的任何解决方案。 数据库是Oracle 10g中。

Answer 1:

有不少建在公共集合类型。 您可以利用这样的其中之一：

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;

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

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')

提示

/*+ cardinality(a, 1) */

只是用来告诉oracle的许多元素如何我们的数组中（如果没有指定，默认将是8K元素的假设）。 正好被设置为合理准确的数字。

Answer 2:

你可以改变一个变量为使用查询CONNECT BY （11g上进行测试，应该万兆+工作）：

SQL> WITH DATA AS (SELECT '100,200,300' txt FROM dual)
  2  SELECT regexp_substr(txt, '[^,]+', 1, LEVEL) item FROM DATA
  3  CONNECT BY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) + 1;

ITEM
--------------------------------------------
100
200
300

然后，您可以将此结果加入到表，如果它是一个标准的看法：

SQL> WITH DATA AS (SELECT '100,200,300' txt FROM dual)
  2  SELECT v.id, dbt.value01
  3    FROM dbt
  4    RIGHT JOIN
  5      (SELECT to_number(regexp_substr(txt, '[^,]+', 1, LEVEL)) ID
  6         FROM DATA
  7       CONNECT BY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) + 1) v
  8       ON dbt.id = v.id;

        ID VALUE01
---------- ----------
       100 Ann
       300 John
       200 

Answer 3:

解决这个的一种方法是动态地创建，然后可以包括在查询中的公共表表达式。 你会瞄准最后synatx是：

with list_of_values as (
  select 100 val from dual union all
  select 200 val from dual union all
  select 300 val from dual union all
  ...)
select
  lov.val,
  ...
from
  list_of_values lov left outer join 
  other_data     t   on (lov.val = t.val)

这是不是很优雅，特别是对大型值的集合，但在其上可能有一些权限的数据库兼容性非常好。

文章来源: Comparing list of values against table