两个具有相似列但主键不同的表

本文介绍了两个具有相似列但主键不同的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有来自两个不同数据库的两个表，并且都包含lastName和firstName列.我需要在两者之间创建JOIN关系. lastName列匹配大约80％的时间，而firstName列仅匹配大约20％的时间.每个表都有完全不同的personID主键.

I have two tables from two different databases, and both contain lastName and firstName columns. I need to create JOINa relationship between the two. The lastName columns match about 80% of the time, while the firstName columns match only about 20% of the time. And each table has totally different personID primary keys.

通常来说，当我向其中一个表中添加外键时，将使用哪些最佳实践"和/或技巧?由于我有大约4,000位不同的人，因此，不省人工的窍门将不胜感激.

Generally speaking, what would be some "best practices" and/or tips to use when I add a foreign key to one of the tables? Since I have about 4,000 distinct persons, any labor-saving tips would be greatly appreciated.

采样不匹配的数据:

db1.table1_____________________ db2.table2_____________________ 23 Williams Fritz 98 Williams Frederick 25 Wilson-Smith James 12 Smith James Wilson 26 Winston Trudy 73 Winston Gertrude

请紧记:有时他们完全匹配，有时却不匹配，有时两个不同的人会有相同的名字/姓氏.

Keep in mind: sometimes they match exactly, often they don't, and sometimes two different people will have the same first/last name.

推荐答案

您可以在多个字段上加入.

You can join on multiple fields.

select * from table1 inner join table2 on table1.firstName = table2.firstName and table1.lastName = table2.lastName

由此您可以确定有多少个重复"名字/姓氏组合.

From this you can determine how many 'duplicate' firstname / last name combos there are.

select table1.firstName, table2.lastName, count(*) from table1 inner join table2 on table1.firstName = table2.firstName and table1.lastName = table2.lastName group by table1.firstName, table2.lastName having count(*) > 1

反过来说，您也可以只确定一次就匹配相同的匹配项:

Conversely, you can also determine the ones which match identically, and only once:

select table1.firstName, table2.lastName from table1 inner join table2 on table1.firstName = table2.firstName and table1.lastName = table2.lastName group by table1.firstName, table2.lastName having count(*) = 1

最后一个查询可能是执行大量外键更新的基础.

And this last query could be the basis for performing the bulk of your foreign key updates.

对于那些在表之间不止一次匹配的名称，除非表中还有其他字段可用于区分它们，否则它们可能需要某种手动干预?

For those names that match more than once between the tables, they'll likely need some sort of manual intervention, unless there are other fields in the table that can be used to differentiate them?