如何在没有完整性约束的情况下链接两个数据库表

Question

>我正在尝试链接两个具有匹配字段的表，但字段的值格式不同。例如，匹配的字段名称为"序列号"，一个表中的值可能是"134A-AC65"，但另一个表中的值可能是"134A_AC65"或任何其他字符组合。问题在于数据库表没有设置任何类型的约束，并且数据是由在现场操作的最终用户手动输入的，因此输入的准确性值得怀疑。到目前为止，我已经找到了两个表中存在的匹配值，所以我知道这些表可以链接，但我需要知道是否有其他匹配值，但由于数据输入不佳，目前可能不匹配。有什么想法吗？谢谢。

Answer 1

值似乎由"数据"和"分隔符"组成。这里成功的关键是知道所有的分隔符是什么。

create table table_1 (
  serial_id varchar(15) primary key
);
insert into table_1 values ('134A-AC65');
insert into table_1 values ('232A/AC65');
insert into table_1 values ('333:A:AC65');
create table table_2 (
  serial_id varchar(15) primary key
);
insert into table_2 values ('134A_AC65');
insert into table_2 values ('232A_AC65');
insert into table_2 values ('333|A|AC65');

五种不同的分隔符，"-"、"_"、"："、"|"、"/"。我只是为了加入而用空格替换它们。

select table_1.serial_id, table_2.serial_id 
from table_1
inner join table_2 
    on translate(table_1.serial_id, '-_:|/', '     ') = translate(table_2.serial_id, '-_:|/', '     ');

这就是PostgreSQL执行连接的方式。（Oracle translate（） 与此类似。

134A-AC65 134A_AC65232A/AC65 232A_AC65333：A：AC65 333|A|AC65

通常，这是您要修复的数据错误类型，因为表达式上的连接往往很慢（ish）。 有不止一种方法可以做到这一点。您可以就地更新值。您可以向每个表添加一列，使用serial_id的"right"值更新该列，然后联接新列。（清理任务留给读者。

Answer 2

您应该向每个表添加一个CanonicalID列。 然后编写查询以根据每个表中的SerialID值设置CanonicalID。 设置所有CanonicalID值后，可以基于这些列执行联接。 喜欢这个：

create table t1 (
    serialid varchar(50),
    ...other columns...
);
alter table t1
add column canonicalid varchar(50);
update table t1
set canonicalid = upper(replace(serialid, '_', '-'));  
/* This might need to be more complex, depending on the kind of problems
you have in the serialid field */

。对表t2执行相同的操作

现在您可以加入t1.canonicalid = t2.canonicalid