Oracle查询:如何比较具有逗号分隔值的不同表的两列 [英] Oracle Query: How to compare two column of different table having comma separated values

问题描述

如何比较具有逗号分隔值的不同表中的两个prod_name列?

How to compare two prod_name columns from different tables that have comma separated values?

这些列具有以逗号分隔的不同顺序的值.要找到匹配项，所有值应与其他表prod_name列值匹配.

These columns are having comma separated values in different order. To find a match, all values should match with other table prod_name column values.

换句话说，这些值在各列中可能以不同的顺序存在，只要这两个列中所有用逗号分隔的值都表示出来，就没关系了.

In other words, the values may exist in different order in columns and that doesn't matter as long as all of the comma separated values are represented in both columns.

TABLE 1:
PRODUCT_ID                         PROD_NAME
================================================
1                                   O,G,E,H,R  
2                                   P,D,H,P,N
3                                   C,D,A,D,P
4                                   E,D,A,D,P


TABLE 2:
PRODUCT_ID  PROD_NAME
======================
5          R,O,G,E H
6          P,D,H,N,P
7          C,D,A,D
8          C,D,A,P,D

预期结果:

1 matches with 5
2 matches with 6
3 matches with 8

推荐答案

您可以使用TRANSLATE和TRIM函数来实现此目标，如下所示.

You can achieve this using TRANSLATE and TRIM function as following.

SQL> with t1 as
  2  (select 1 as product_id, 'O,G,E,H,R' as prod_name from dual union all
  3  select 2, 'P,D,H,P,N' from dual union all
  4  select 3, 'C,D,A,D,P' from dual union all
  5  select 4, 'E,D,A,D,P' from dual),
  6  t2 as
  7  (select 5 as product_id, 'R,O,G,E,H' as prod_name from dual union all
  8  select 6 as product_id, 'P,D,H,N,P' as prod_name from dual union all
  9  select 7 as product_id, 'C,D,A,D' as prod_name from dual union all
 10  select 8 as product_id, 'C,D,A,P,D' as prod_name from dual)
 11  SELECT
 12      T1.PRODUCT_ID,
 13      T2.PRODUCT_ID
 14  FROM
 15      T1
 16      JOIN T2 ON TRIM('#' FROM TRANSLATE(T1.PROD_NAME, T2.PROD_NAME, '#')) IS NULL;

PRODUCT_ID PRODUCT_ID
---------- ----------
         1          5
         2          6
         3          8

SQL>

此查询认为您的prod_name的任何值中都不包含#字符.

This query considers that your prod_name do not have # character in any of the value.

干杯！

这篇关于Oracle查询:如何比较具有逗号分隔值的不同表的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！