使用值连接两个表的SQL查询，用逗号分隔

Question

我有两个表有值

Master_table

id refer_id   value1 value2
1   C_1,C_2     x   y
2   C_3,C_4     Z   W
3   C_6         O   P
4   C_4,C-2     Q   R
5   C_1,C-3     S   T

Secondary_table

id  value3
C_1  A
C_2  B
C_3  C
C_4  D
C_5  E
C-6  F

在这里，我应该组合这两个表，并使用主表的refer_id，并将其引用到辅助表以获得如下所示的值

value1 value2 value3
X        Y     A
X        Y     B
Z        W     C
Z        W     D
O        P     F
Q        R     D
Q        R     B

我使用下面的查询将refer_id从主表中分离出来，但是我不知道如何从辅助表中检索它

distinct(TRIM(regexp_substr(refer_id,'[^,]+', 1, level))) from Master_table connect by regexp_substr(refer_id, '[^,]+', 1, level) is not null;

Answer 1

Oracle安装

CREATE TABLE Master_table ( id, refer_id, value1, value2 ) AS
SELECT 1, 'C_1,C_2', 'x', 'y' FROM DUAL UNION ALL
SELECT 2, 'C_3,C_4', 'Z', 'W' FROM DUAL UNION ALL
SELECT 3, 'C_6',     'O', 'P' FROM DUAL UNION ALL
SELECT 4, 'C_4,C_2', 'Q', 'R' FROM DUAL UNION ALL
SELECT 5, 'C_1,C_3', 'S', 'T' FROM DUAL;

CREATE TABLE Secondary_table ( id, value3 ) AS
SELECT 'C_1', 'A' FROM DUAL UNION ALL
SELECT 'C_2', 'B' FROM DUAL UNION ALL
SELECT 'C_3', 'C' FROM DUAL UNION ALL
SELECT 'C_4', 'D' FROM DUAL UNION ALL
SELECT 'C_5', 'E' FROM DUAL UNION ALL
SELECT 'C_6', 'F' FROM DUAL;

查询

SELECT value1, value2, value3
FROM   master_table m
       INNER JOIN
       secondary_table s
       ON ( ',' || m.refer_id || ',' LIKE '%,' || s.id || ',%' );

输出

VALUE1 VALUE2 VALUE3
------ ------ ------
x      y      A      
x      y      B      
Z      W      C      
Z      W      D      
O      P      F      
Q      R      B      
Q      R      D      
S      T      A      
S      T      C  

或者您可以使用集合：

Oracle安装

用于将字符串拆分为其分隔值的短助手类型和函数：

CREATE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/

CREATE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
  p_result       stringlist := stringlist();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

查询

SELECT value1, value2, value3
FROM   master_table m
       INNER JOIN
       secondary_table s
       ON ( s.id MEMBER OF split_String( m.refer_id ) );

(与上述输出相同)