使用预定义值列表作为SQL表的SQL语句 [英] SQL-Statement to use predefined values list as an SQL-table

问题描述

我有一个ID列表.例如(1、2、10).可以将该列表用作Oracle中的表吗?

I have a list of ids. e.g. (1, 2, 10). Is it possible to use that list as a table in Oracle ?

我的意思是这样的:

select * from (1, 2, 10) as x, some_table
where x not in some_table.value

推荐答案

select to_number(regexp_substr(str, '[^,]+', 1, level)) ids
--put your comma-delimited list in the string below
  from (select '0,12,2,3,4,54,6,7,8,97' as str from dual)
connect by level <= length(regexp_replace(str, '[^,]+')) + 1
minus
--here comes select from some_table
select level from dual connect by level <= 10;

这篇关于使用预定义值列表作为SQL表的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！