号码上的加入包含在字符串中 [英] Join on number is contained in string

问题描述

在我的oracle数据库中，我有2个表，并且我必须将它们连接起来，其中表A中的列ID是数字，而表B中的列SCIDS是varchar2.这是一些示例:

In my oracle database i have 2 tables and i have to join them where in TABLE A i have column ID which is number, and TABLE B where i have column SCIDS which is varchar2. Here is some example:

TABLEA.ID | TABLEB.SCIDS
162       | 162,163
162       | 555,162,33
161       | 161

我尝试过的一些sql

Some sql i tried

select A.ID
from TABLEA A
    left JOIN TABLEB B 
where "," || S.ID in "," || B.SCIDS

这不起作用，它仅返回SCIDS中非数组"的记录，例如第三记录161.

This does not work it return only the records that are not "array" in SCIDS e.g 3rd record 161.

如何退还全部?

推荐答案

您可以尝试一下，尽管效率不高

You could try this, though not completely efficient one

with dist_tablea
     AS (SELECT DISTINCT ID
           FROM TABLEA)

SELECT a.ID, B.SCIDS
  FROM dist_tablea a LEFT JOIN TABLEB b ON REGEXP_LIKE (b.SCIDS, '(^|,)'||a.id||'(,|$)')

输出:

    ID  SCIDS
-------  -------
    161 161
    162 162,163
    162 555,162,33

这篇关于号码上的加入包含在字符串中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！