两个具有相似列但主键不同的表 [英] Two tables with similar columns but different primary keys

问题描述

我有来自两个不同数据库的两个表，并且都包含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?

这篇关于两个具有相似列但主键不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！