TSQL:连接列,但一列中的行具有多个值 [英] TSQL: Join columns, but rows in one column have multiple values

查看:110
本文介绍了TSQL:连接列,但一列中的行具有多个值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个查询,我必须将两个表连接起来.假设T1和T2. T1有一个带有ID的列.而且每一行只有一个Id值.但是,第二张桌子正是我在努力的地方.在T2中,有一列带有Id,但是一行可能有多个Id. 因此,例如T1.col1的值是:737382.但是T2.col2可以在一行中包含以下条目:737382; 239112; 2838210; 9923834; 2388342; ...

I have a query where I have to join two tables. Lets say T1 and T2. T1 has a column with an Id. And every row has only one Id value. However, the second table is where i'm struggling. in T2 there is a column with the Id's but it can be possible that one row has multiple of those Id's. So as example T1.col1 has this value: 737382. But T2.col2 can have this entries in one row: 737382;239112;2838210;9923834;2388342;...

我知道此结构违反了1NF和东西.但是我无法更改数据或数据结构中的任何内容. 现在我想做的就是加入这两个表.如果我想告诉我任何有关的信息以回答我的问题,请告诉我.太晚了,我的大脑筋疲力尽〜.〜

I know that this structure violates the 1NF and stuff. But i cannot change anything in the data or structure of the data. now what i want to do is to join this two tables. Please let me know if i'm missing to tell any relevant information in order to answer my question. its late and my brain is exhausted ~.~

推荐答案

尝试

    select tab2.*   -- whatever
      from t1 tab1
inner join t2 tab2 on ( ';'||tab2.col2||';' like '%;'||tab1.col1||';%' )
         ;

附加的;字符用于避免联接条件中的析取.

the extra affixed ; characters serve to avoid disjunctions in the join condition.

这篇关于TSQL:连接列,但一列中的行具有多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆