Oracle 查询以选择具有唯一元素和跨行唯一元素的行

嗨，

我需要一个查询，它只显示具有唯一元素（逗号分隔）的行，并且不具有存在于另一行中的元素。如果元素存在于多个行中，则必须考虑包含最大元素的行。

示例：

Create table test (col1 number, col2 varchar2(4000));

Insert into TEST Values (1, '1、2、3、4');
Insert into TEST Values (2, '2,3,4,5,3,6');
Insert into TEST Values (3, '11、12、13、14、15');
Insert into TEST Values (4, '20,21,22,13');
Insert into TEST Values (5, '7、8、9、10');
COMMIT;

选择查询必须返回包含元素的行
1、2、3、4
11、12、13、14、15
7、8、9、10

剩余行
2、3、4、5、3、6应被忽略，因为它具有重复的值3。
20,21,22,13 should be ignored because value 13 is repeated in another row 11、12、13、14、15 and number of elements are greater than current row elements 5> 4 (20,21,22,13).

这个要求看起来很傻，但是我正在处理一个返回这样一个结果集的连接查询，并且需要按上面的方式进行过滤。假设测试表中的行计数以百万计。

谢谢，
依姆兰。

你的两个例子似乎互相矛盾。

一、二、三、四对二、三、四、五、六

2、3、4、5、6不是要保留的吗？因为它比1、2、3、4要长？

在任何情况下，您都会遇到一些麻烦，因为检查重复项的唯一方法是解析并考虑每一行中的每个项，并将它与每一行中的每个项进行比较。

这不可能达到好的规模。但这里是：

SQL> drop table test purge;

Table dropped.

SQL>
SQL> Create table test (col1 number, col2 varchar2(4000));

Table created.

SQL>
SQL> Insert into TEST Values (1, '1,2,3,4');

1 row created.

SQL> Insert into TEST Values (2, '2,3,4,5,3,6');

1 row created.

SQL> Insert into TEST Values (3, '11,12,13,14,15');

1 row created.

SQL> Insert into TEST Values (4, '20,21,22,13');

1 row created.

SQL> Insert into TEST Values (5, '7,8,9,10');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> alter table test add terms generated always as  ( 1+length(col2)-length(replace(col2,',')) );

Table altered.

SQL>
SQL> create or replace
  2  type pair as object ( x1 int, x2 int );
  3  /
create or replace
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


SQL>
SQL> create or replace
  2  type instr_pairs as table of pair
  3  /

Type created.

SQL>
SQL> col col2 format a30
SQL> col each_item format a10
SQL>
SQL> drop table parsed_items purge;

Table dropped.

SQL>
SQL> create table parsed_items as
  2  select col1, col2, terms, x1,x2, substr(col2,x1,x2-x1) each_item
  3  from test t1,
  4       table(cast(multiset(select pair(case when level=1 then 1 else 1+instr(col2||',',',',1,level-1) end,
  5                                       instr(col2||',',',',1,level)
  6                                      )
  7                           from dual connect by level <= t1.terms) as instr_pairs)) ;

Table created.

SQL>
SQL>
SQL> select distinct
  2    case
  3      when match_found = 0 then col1
  4      when match_found != 0 and terms >= matching_terms then col1
  5      when match_found != 0 and terms < matching_terms then  matching_col1
  6    end col1,
  7    case
  8      when match_found = 0 then col2
  9      when match_found != 0 and terms >= matching_terms then col2
 10      when match_found != 0 and terms < matching_terms then  matching_col2
 11    end col2
 12  from (
 13  select p1.col1, p1.col2, p1.terms,
 14         count(p2.col1) match_found,
 15         max(p2.terms) matching_terms,
 16         max(p2.col2) matching_col2,
 17         max(p2.col1) matching_col1
 18  from parsed_items p1,
 19       parsed_items p2
 20  where  p1.each_item = p2.each_item(+)
 21  and    p1.col1 != p2.col1(+)
 22  group by p1.col1, p1.col2, p1.terms
 23  );

      COL1 COL2
---------- ------------------------------
         3 11,12,13,14,15
         2 2,3,4,5,3,6
         5

3 rows selected.

SQL>
SQL>

我相信最后一个查询是可以改进的，但是真的...那不是成本的问题。它是两个大数据集之间的笛卡尔积，这将会引起最大的痛苦。

在进行连接之前，重新思考一下是否可以进行所需的过滤