sql - 通过检查一张表中的各个字段来进行值(value)推导

我有下面的表(表1是基表)，我需要派生一个新字段“catid”和“origid”。 “Catid”只是(id和nv)的串联，但是origid是通过检查表1中是否存在表2中的(sid或sid2或sid3或sid4)而得出的。如果存在它们，只需将table1中的“catid”分配给他们表1:id nv sid sid2 sid3 sid4---------------------------------------1 a 11 A1 B1 C12 b 22 A2 B2 (null)3 c 33 A3 B3 C3表2:id nv sid sid2 sid3 sid4---------------------------------------4 x 11 (null) B1 C15 y (null) A2 B2 (null)6 z 33 A3 (null) C3想要的表3:id nv sid sid2 sid3 sid4 catid origid--------------------------------------------------1 a 11 A1 B1 C1 1a 1a2 b 22 A2 B2 (null) 2b 2b3 c 33 A3 B3 C3 3c 3c4 x 11 (null) B1 C1 4x 1a5 y (null) A2 B2 (null) 5y 2b6 z 33 A3 (null) C3 6z 3c表格1create table table1 ( id number, nv varchar2(2) sid varchar2(3), sid2 varchar2(3), sid3 varchar2(3), sid4 varchar2(3));Table 2create table table2 ( id number, nv varchar2(2), sid varchar2(3), sid2 varchar2(3), sid3 varchar2(3), sid4 varchar2(3));insert into table1 values(1, 'a', '11','A1','B1','C1');insert into table1 values(2, 'b', '22','A2','B2','');insert into table1 values(3, 'c', '33','A3','B3','C3');insert into table2 values(4, 'x', '11','','B1','C1');insert into table2 values(5, 'y', '','A2','B2','');insert into table2 values(6, 'z', '33','A3','','C3');谢谢 最佳答案 您需要某种join来执行此操作。这是一种方法:with t1 as ( select id, nv, sid, sid2, sid3, sid4, id || nv as catid, id || nv as origid from table1 )select id, nv, sid, sid2, sid3, sid4, catid, origidfrom t1union allselect id, nv, sid, sid2, sid3, sid4, id || nv as catid, (select t1.origid from t1 where (t1.sid = t2.sid or t1.sid2 = t2.sid2 or t1.sid3 = t2.sid3 or t1.sid4 = t2.sid4 ) and rownum = 1 ) as origidfrom table2 t2; Here是db 小提琴。关于sql - 通过检查一张表中的各个字段来进行值(value)推导，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54827016/