logo标签列表

如何在MySQL中选择两个不同表中相似的行？（是否可行？）

mysqlselect
4
我有两个表格，我想获取所有与同名或相似拼写的学校。例如：
我的表格A:

学校

            

Olde School        
New School    
Other, C.S. School   
Main School
Too Cool for School

我的表_b:

学校

Old School
New ES    
Other School 
Main School  
Hardknocks School

有没有可能编写一个SELECT查询语句，在这两个表中找到拼写相似的学校。是否可以在列上使用LIKE或通配符？

例如：

SELECT  my_table_a.school, my_table_b.school
FROM ` my_table_a` ,  my_table_b
WHERE  my_table_a.directory_school_name_09_10 LIKE  my_table_b.school

我尝试了上述语句并使用了我的真实表格，结果只是'='运算符的输出。

基本上，我想获取每个表列中前4所学校。（当然，在现实世界中，我不会知道前4所学校是相似的）。

我正在尝试做的事情是否可能？

4个回答
6

如果你想要实现Levenshtein Distance算法的UDF，你可以查看 "codejanitor.com: Levenshtein Distance as a MySQL Stored Function":

CREATE FUNCTION LEVENSHTEIN (s1 VARCHAR(255), s2 VARCHAR(255))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
  DECLARE s1_char CHAR;
  DECLARE cv0, cv1 VARBINARY(256);
  SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
  IF s1 = s2 THEN
    RETURN 0;
  ELSEIF s1_len = 0 THEN
    RETURN s2_len;
  ELSEIF s2_len = 0 THEN
    RETURN s1_len;
  ELSE
    WHILE j <= s2_len DO
      SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
    END WHILE;
    WHILE i <= s1_len DO
      SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
      WHILE j <= s2_len DO
        SET c = c + 1;
        IF s1_char = SUBSTRING(s2, j, 1) THEN SET cost = 0; ELSE SET cost = 1; END IF;
        SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
        IF c > c_temp THEN SET c = c_temp; END IF;
        SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
        IF c > c_temp THEN SET c = c_temp; END IF;
        SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
      END WHILE;
      SET cv1 = cv0, i = i + 1;
    END WHILE;
  END IF;
  RETURN c;
END

现在让我们构建一个测试用例，使用您在问题中提供的数据：
CREATE TABLE table_a (name varchar(20));
CREATE TABLE table_b (name varchar(20));

INSERT INTO table_a VALUES('Olde School');      
INSERT INTO table_a VALUES('New School');
INSERT INTO table_a VALUES('Other, C.S. School');
INSERT INTO table_a VALUES('Main School');
INSERT INTO table_a VALUES('Too Cool for School');

INSERT INTO table_b VALUES('Old School');
INSERT INTO table_b VALUES('New ES');
INSERT INTO table_b VALUES('Other School');
INSERT INTO table_b VALUES('Main School');
INSERT INTO table_b VALUES('Hardknocks School');

然后：

SELECT     *
FROM       table_a a
LEFT JOIN  table_b b ON (a.name = b.name);

显然，返回一个完全匹配学校名称的结果：

+---------------------+-------------+
| name                | name        |
+---------------------+-------------+
| Olde School         | NULL        |
| New School          | NULL        |
| Other, C.S. School  | NULL        |
| Main School         | Main School |
| Too Cool for School | NULL        |
+---------------------+-------------+
5 rows in set (0.00 sec)

现在我们可以尝试使用LEVENSHTEIN函数来返回编辑距离小于等于2个字符的学校名称：
SELECT     *
FROM       table_a a
LEFT JOIN  table_b b ON (LEVENSHTEIN(a.name, b.name) <= 2);

+---------------------+-------------+
| name                | name        |
+---------------------+-------------+
| Olde School         | Old School  |
| New School          | NULL        |
| Other, C.S. School  | NULL        |
| Main School         | Main School |
| Too Cool for School | NULL        |
+---------------------+-------------+
5 rows in set (0.08 sec)

现在将编辑距离阈值设为<= 3：

SELECT     *
FROM       table_a a
LEFT JOIN  table_b b ON (LEVENSHTEIN(a.name, b.name) <= 3);

我们得到以下结果：
+---------------------+--------------+
| name                | name         |
+---------------------+--------------+
| Olde School         | Old School   |
| Olde School         | Other School |
| New School          | Old School   |
| Other, C.S. School  | NULL         |
| Main School         | Main School  |
| Too Cool for School | NULL         |
+---------------------+--------------+
6 rows in set (0.06 sec)

请注意，这次 Olde School 也匹配了 Other School ，而 New School 也匹配了 Old School 。这些可能是误报，表明定义阈值非常重要，以避免不正确的匹配。
解决此问题的一种常见技术是在应用阈值时考虑字符串的长度。实际上，我引用的网站还提供了一个 LEVENSHTEIN_RATIO 函数，该函数基于字符串的长度返回编辑差异的比率（作为百分比）。
2
0

你可以试试这个

select test1.test1name,test2.test2name from test1  join test2 on
soundex(SUBSTRING_INDEX(test1.test1name, ' ',1))=
soundex(SUBSTRING_INDEX(test2.test2name, ' ',1))

只需根据您的表名和字段名填写即可。

0

这个问题比较广泛。什么样的内容算是相似呢？无论如何，虽然我不能给你一个确切的答案，但我可以说你可能想要使用全文搜索：http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html 或者使用一些技巧，使用LIKE通配符操作符：%。

... LIKE "new%school"

将匹配"new school"和"new darn school"。

嗨，Mike感谢你的建议。我会尝试看看能否让其中一个起作用。这不是一个非常精确的问题。大多数项目都不太相似，这使得解决问题变得困难。然而，表格中的一些项目是密切相关的。例如，很多条目是“blah EC”，在另一个表格中则是“blah ES”。因此，我将从较容易的开始。谢谢。 - Laxmidi
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接