oracle sql：检查值列表中的个体是否存在于表中

发布于 2024-11-08 04:12:26 字数 400 浏览 0 评论 0原文

有人给我邮寄了一份包含代码的列表，我需要检查每个代码是否存在于 oracle 10 数据库表中。

列表看起来像：

code1, code2, code3

当然对于列表中的每一项我都可以做

select id from my_table where code = 'code1'.

但这会很耗时，而且不是很优雅。我想报告一个类似的列表：

code1        present
code2        X
code3        X
code4        present

我隐约知道oracle的With语句，但我不确定如何将它与值列表而不是子查询一起使用。

原文

Somebody mailed me a list with codes, and I need to check whether each code does exist in an oracle 10 database table.

list looks something like:

code1, code2, code3

Of course for every item in the list i can do

select id from my_table where code = 'code1'.

But this would be time consuming, and not a very elegant. I would like to report back a list like:

code1        present
code2        X
code3        X
code4        present

I'm vaguely aware of oracle's With statement, but I'm not sure how to use it with a list of values instead of a subquery.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

残月升风 2024-11-15 04:12:26

逆过程可以这样实现（查看数据库中的所有代码，并检查它们是否在您同事提供的列表中）：

select code, case when code in ('code1', 'code2', 'code3') 
                  then 'present' 
                  else 'X' end
from my_table;

如果这对您不起作用，您可以尝试以下操作：

-- you need a special type for your request. Adapt dimensions if necessary
create type codes as varray(100) of varchar(100);

select c.column_value, case when exists (
  select 1 
  from my_table m 
  where m.code = c.column_value
) then 'present' else 'X' end
from table(codes('code1', 'code2', 'code3')) c;

-- drop that type again
drop type codes;

The inverse can be achieved like this (see all codes from the database, and check whether they are in the list provided by your colleague):

select code, case when code in ('code1', 'code2', 'code3') 
                  then 'present' 
                  else 'X' end
from my_table;

If that won't work for you, you can try this:

-- you need a special type for your request. Adapt dimensions if necessary
create type codes as varray(100) of varchar(100);

select c.column_value, case when exists (
  select 1 
  from my_table m 
  where m.code = c.column_value
) then 'present' else 'X' end
from table(codes('code1', 'code2', 'code3')) c;

-- drop that type again
drop type codes;

回复收藏 0 原文

~没有更多了~