SELECT from table with Varying IN list in WHERE 子句

2021-12-06 00:00:00 sql oracle where-in xmltable

我正在处理的项目中遇到一个问题，我不能给你实际的代码，但我已经创建了一个可执行的示例代码，如下所示

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是两张表

  1. temp 表包含逗号分隔的 ID 列表，即 VARCHAR2
  2. temp_id 表包含实际的 ID，即 NUMBER
  1. temp table contains comma separated list of ids which is VARCHAR2
  2. 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') NOT 与 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.

相关文章