从带有WHERE子句中的IN列表变化的表中进行SELECT

本文介绍了从带有WHERE子句中的IN列表变化的表中进行SELECT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在处理的项目遇到问题，我无法给您实际的代码，但是我创建了如下的可执行示例代码

I am facing a issue in project I am working on, I can not give you actual code but I have created a executable sample code as below

temp和temp_id是两个表

temp表包含以逗号分隔的ID列表，该列表为VARCHAR2

temp_id表包含的实际ID为NUMBER

temp table contains comma separated list of ids which is VARCHAR2

temp_id table contains actual ids which is NUMBER

我想通过从temp表中的ID的逗号分隔列表中获取ids来搜索temp_id表中的行

I want to search rows from temp_id table by getting ids from comma separated list of ids from temp table

//DDLs to create table CREATE TABLE temp(ids VARCHAR2(4000)); CREATE TABLE temp_id(data_id NUMBER); //DMLs to populate test data INSERT INTO temp VALUES('1, 2, 3'); INSERT INTO temp_id VALUES(1); INSERT INTO temp_id VALUES(2); INSERT INTO temp_id VALUES(3); INSERT INTO temp_id VALUES(4); INSERT INTO temp_id VALUES(5);

此查询不起作用

SELECT * FROM temp_id WHERE data_id IN (SELECT to_number(COLUMN_VALUE) FROM XMLTABLE(SELECT ids FROM temp));

有效查询

SELECT * FROM temp_id WHERE data_id IN (SELECT to_number(COLUMN_VALUE) FROM XMLTABLE('1, 2, 3'));

以上两个查询之间的区别是，我在第一个查询中使用的是temp表中的列，而在第二个查询中使用的是直接引号varchar2.没有得到为什么不起作用的原因?我在想什么吗?我认为可能是某些数据类型不匹配，但无法弄清楚.

Here difference between above two queries is I am using column from temp table in first query and direct quoted varchar2 in second query. Not getting the reason why is not working? Is I am missing something? I think there might be some datatype mismatch but not able to figure it out.

推荐答案

您的要求称为不断变化的IN列表.参见 变化IN WHERE子句中的值列表

Your requirement is called as Varying IN-lists. See Varying IN list of values in WHERE clause

原因: IN ('1, 2, 3')与IN (1, 2, 3)或IN('1', '2', '3')

因此

SELECT * FROM temp_id WHERE data_id IN(SELECT ids from temp);

SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);

与

SELECT * FROM temp_id WHERE data_id IN('1、2、3');

SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');

这将引发错误 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);

SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);

这将为您提供正确的输出-

which would give you correct output -

SQL> SELECT * FROM temp_id WHERE data_id IN(1, 2, 3); DATA_ID ---------- 1 2 3

解决方案:

根据您的要求，您可以像这样实现它-

For your requirement, you can achieve it like this -

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

或者，您可以创建自己的 TABLE函数或管道功能来实现此目的.您的目标应该是 将逗号分隔的IN列表分成多行 .您的操作方式取决于您！

Alternatively, you can create your own TABLE function or a Pipelined function to achieve this. Your goal should be to split the comma-separated IN list into multiple rows. How you do it is up to you!

工作演示

让我们以SCOTT模式中的标准 EMP 表为例.

Let's take an example of the standard EMP table in SCOTT schema.

我有一个字符串工作列表，我想计算这些工作的员工数:

I have a list of jobs in a string, and I want to count the employees for those jobs:

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列表不断变化.

Oh! What happened? The standard emp table should give an output 10. The reason is that the varying IN list.

让我们看看正确的方法:

Let's see the correct way:

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.