您的要求称为变化的 IN 列表. See WHERE 子句中的 IN 值列表发生变化 http://lalitkumarb.wordpress.com/2015/01/02/varying-in-list-of-values-in-where-clause/
Reason : IN ('1, 2, 3')
is NOT与...一样IN (1, 2, 3)
OR IN('1', '2', '3')
Hence,
SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);
与
SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');
这会抛出一个error ORA-01722: invalid number
-
SQL> SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');
SELECT * FROM temp_id WHERE data_id IN('1, 2, 3')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);
SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp)
*
ERROR at line 1:
ORA-01722: invalid number
不一样
SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);
这会给你正确的输出 -
SQL> SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);
DATA_ID
----------
1
2
3
解决方案 :
根据您的要求,您可以这样实现 -
SQL> SELECT * FROM temp;
IDS
--------------------------------------------------------------
1, 2, 3
SQL> SELECT * FROM temp_id;
DATA_ID
----------
1
2
3
4
5
SQL> WITH data AS
2 (SELECT to_number(trim(regexp_substr(ids, '[^,]+', 1, LEVEL))) ids
3 FROM temp
4 CONNECT BY instr(ids, ',', 1, LEVEL - 1) > 0
5 )
6 SELECT * FROM temp_id WHERE data_id IN
7 (SELECT ids FROM data
8 )
9 /
DATA_ID
----------
1
2
3
或者,您可以创建自己的表格功能 or a 流水线函数为了达成这个。你的目标应该是将逗号分隔的 IN 列表拆分为多行 http://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/。你怎么做取决于你!
工作演示
我们举个标准的例子EMP表中SCOTT
schema.
我有一个字符串中的工作列表,我想计算这些工作的员工数量:
SQL> SET serveroutput ON
SQL> DECLARE
2 str VARCHAR2(100);
3 cnt NUMBER;
4 BEGIN
5 str := q'[CLERK,SALESMAN,ANALYST]';
6 SELECT COUNT(*) INTO cnt FROM emp WHERE JOB IN (str);
7 dbms_output.put_line('The total count is '||cnt);
8 END;
9 /
The total count is 0
PL/SQL procedure successfully completed.
哦!发生了什么?标准 emp 表应该给出输出 10。原因是变化的 IN 列表.
我们来看看正确的做法:
SQL> SET serveroutput ON
SQL> DECLARE
2 str VARCHAR2(100);
3 cnt NUMBER;
4 BEGIN
5 str := q'[CLERK,SALESMAN,ANALYST]';
6 SELECT COUNT(*)
7 INTO cnt
8 FROM emp
9 WHERE job IN
10 (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL))
11 FROM dual
12 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
13 );
14 dbms_output.put_line('The total count is '||cnt);
15 END;
16 /
The total count is 10
PL/SQL procedure successfully completed.