连接两个不同的mySQL表的最佳方法 -- 为Django做准备的Python

Question

table a (t_a):
id  name    last    first   email           state   country
0   sklass  klass   steve   sklass@foo.com  in      uk
1   jabid   abid    john    abid@foo.com    ny      us
2   jcolle  colle   john    jcolle@foo.com  wi      us


table b (t_b):
id  sn      given   nick    email           l   c   
0   steven  klass   steve   sklass@foo.com  in  uk
1   john    abid    -       abid_j@foo.com  ny  us
2   johnny  colle   john    jcolle@foo.com  wi  us
3   john    abid    -       abid@foo.com    ny  us

上面列出的内容是一个简化版的MySQL表格，包括列和行。看这两个表格，你会发现只要严格比较这些值（不考虑ID），就能找到匹配的值。

t_a     t_b
0       0
1       3
2       2
-       1

我最终想要在Django中实现这个功能——我不确定这是否重要。之前我用纯Python做过这个，方法是删除旧数据，然后创建三个新表。我想改变这种做法（下面有提到），因为我发现时间会改变很多事情，人们也会来来去去。以前我只是重新生成数据，但现在我想记录人们离开的时间，而不是简单地替换（删除）数据。我认为通过SQL更新数据会更优雅，并且能保留历史记录。

我想知道如何直接从MySQL获得这个合并的结果（无论是通过SQL函数还是新表的构建），以以下方式合并数据。我想用纯SQL来实现（我相信这样我也能在Django中做到）。所以我在寻找一个满足以下条件的解决方案：

1. 有一个最小匹配数（min_match），定义了两个行之间必须对齐的最小匹配数量，才能被认为是有效的。
2. 虽然表格的长度可能不同，但它们之间是一对一的映射。换句话说，许多对一个的情况可能不会发生（至少现在不会）。

我的背景是Python，对我来说，最简单的方法就是对较短的表进行一个循环，然后对另一个表进行一个循环，查看匹配的数量。在代码中，这看起来是这样的。

t_a = [ ["sklass", "klass", "steve", "sklass@foo.com", "in", "uk", ],
        ["jabid", "abid", "john", "abid@foo.com", "ny", "us", ],
        ["jcolle", "colle", "john", "jcolle@foo.com", "wi", "us", ], ]

t_b = [ ["steven", "klass", "steve", "sklass@foo.com", "in", "uk",],
        ["john", "abid", "abid_j@foo.com", "ny", "us",],
        ["johnny", "colle", "john", "jcolle@foo.com", "wi", "us",],
        ["john", "abid", "abid@foo.com", "ny", "us",], ]

min_match = 3

for person_a in t_a:
    match = 0
    match_pct = 0.0
    match_a_index = t_a.index(person_a)
    for person_b in t_b:
        new_match_count = len(list(set(person_a) & set(person_b)))
        if new_match_count > match:
            match = new_match_count
            match_b_index = t_b.index(person_b)
            match_pct = "%.2f" % (float(new_match_count) / \
              float(len(set(person_a + person_b))) * 100)
    if match >= min_match:
        print match_a_index, match_b_index #, match_pct, match

评论中有人问，为什么不直接通过电子邮件地址来连接。我不一定知道某一列中的值是否会匹配。我确定在t_a中的某一行的值会与t_b中的某一行的值匹配。我想要t_a中某一行与t_b中最可能匹配的行，并且只有在匹配数量超过最小匹配数时才考虑。

Answer 1

你可以直接在MySQL中通过一个游标来实现这个功能，游标是通过一个存储过程来执行的。

DELIMITER $$
CREATE PROCEDURE `proc_name`()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a_id BIGINT UNSIGNED;
  DECLARE b_id BIGINT UNSIGNED;
  DECLARE x_count INT;

  -- something like the following
  DECLARE cur1 CURSOR FOR SELECT t_a.id, t_b.id FROM t_a, t_b WHERE t_a.email = t_b.email;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SELECT COUNT(*) INTO x_count FROM t_a, t_b WHERE t_a.email = t_b.email;

  IF(x_count > <some_min_value>) THEN

    OPEN cur1;

    REPEAT
      FETCH cur1 INTO a_id, b_id;
      IF NOT done THEN

        -- do something here like update rows, remove rows, etc.
        -- a_id and b_id hold the two id values for the two tables which
        -- I assume to be primary keys

      END IF;
    UNTIL done END REPEAT;

    CLOSE cur1;

  END IF;
END
$$