在postgresQL中工作,我有一个代码值列,其中在一个单元格中有两个值:
CREATE TABLE test.table1(Site varchar(50), CodeValue1 varchar(50));
INSERT INTO test.table1
VALUES ('A', '1005'),
('B', '1006|1007'),
('C', '1008');
我想将代码值列连接到第二个表,如:
CREATE TABLE test.table2(Note varchar(50), CodeValue2 varchar(50));
INSERT INTO test.table2
VALUES ('Note1','1005'),
('Note2', '1006'),
('Note3', '1007');
--this does not work:
select * from test.table1
left join test.table2 on table2.CodeValue2 = table1.Codevalue1
是否有一种方法可以查询'1006|1007',以便连接在1006或1007中工作,而不会将表1拆分为更多行。我想要的输出是这样的:
这是我迄今为止最好的方法,使用子查询,我认为这将很好地满足我的需求,但渴望听到是否有其他解决方案:
select * from (
select Site
, unnest(string_to_array(CodeValue1, '|')) as CV
from test.table1) t1
left join (select * from test.table2) t2 on t2.CodeValue2 = t1.CV);
存储分隔字符串(table1.codevalue1
)是一个非常糟糕的主意,它几乎会在针对它编写的每个查询中导致相同类型的问题。更好的方法是将数据规范化,并将每个值存储为单独的行。但是对于你的直接问题,是否可以在不将table1分成更多行的情况下写入-是的,可以:
select t1.site, t2.note, t2.codevalue2
from table1 t1
join table2 t2
on ( position ('|' || t2.codevalue2 || '|' in '|' || t1.codevalue1 || '|') > 0 );
但我肯定不想工作的
on
条件,和它的变体。你最近的尝试(作为你的答案)是接近。它失败是因为outer join
导致行('c', 1008, null, null)也被返回。你可以通过做inner join
来纠正它。最后,您可以通过消除最外层的select * from (...
来简化为更简单的版本。这种格式很少是必要的。只是:
select t1.site, t2.note, t2.codevalue2
from table2 t2
join (select site, string_to_table(codevalue1, '|') cv from table1) t1
on t1.cv = t2.codevalue2;
注意,string_to_table(...)
函数代替unnest(string_to_array(...))
函数。
在这里查看演示。