如何查询两张表中满足至少3个字符匹配的数据？

我明白你现在的需求：要从两张表中找出满足至少3个字符匹配的记录，之前参考了一些例子没得到想要的结果，下面我来给你针对性的解决方案。

首先先明确你的数据情况：

• 表1（假设命名为table1）的Assets列数据：MADFG, NGGS_Data, KTL_GAS_LCN, FI
• 表2（假设命名为table2）的Asset列数据：VERT, NGS, KTLGAS, FIP
• 期望输出是存在至少3个匹配字符的配对：

  Asset_table1	Asset_table2
  NGGS_Data	NGS
  KTL_GAS_LCN	KTLGAS

方案1：匹配连续3个字符（贴合你的示例输出） #

这种方法检查两个字符串是否存在长度≥3的连续公共子串，和你的示例输出逻辑完全匹配。

SQL Server版本

WITH Table1Substrings AS (
    SELECT 
        Assets AS Asset_table1,
        SUBSTRING(Assets, n, 3) AS Substring
    FROM table1
    CROSS JOIN (
        SELECT number FROM master..spt_values 
        WHERE type = 'P' AND number BETWEEN 1 AND LEN(Assets)-2
    ) AS nums(n)
    WHERE LEN(Assets) >= 3
),
Table2Substrings AS (
    SELECT 
        Asset AS Asset_table2,
        SUBSTRING(Asset, n, 3) AS Substring
    FROM table2
    CROSS JOIN (
        SELECT number FROM master..spt_values 
        WHERE type = 'P' AND number BETWEEN 1 AND LEN(Asset)-2
    ) AS nums(n)
    WHERE LEN(Asset) >= 3
)
SELECT DISTINCT 
    t1.Asset_table1,
    t2.Asset_table2
FROM Table1Substrings t1
JOIN Table2Substrings t2 ON t1.Substring = t2.Substring;

MySQL版本

WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n+1 FROM nums WHERE n <= 20 -- 假设字符串最长不超过20位
),
Table1Substrings AS (
    SELECT 
        Assets AS Asset_table1,
        SUBSTRING(Assets, n, 3) AS Substring
    FROM table1
    JOIN nums ON n <= LENGTH(Assets)-2
    WHERE LENGTH(Assets) >= 3
),
Table2Substrings AS (
    SELECT 
        Asset AS Asset_table2,
        SUBSTRING(Asset, n, 3) AS Substring
    FROM table2
    JOIN nums ON n <= LENGTH(Asset)-2
    WHERE LENGTH(Asset) >= 3
)
SELECT DISTINCT 
    t1.Asset_table1,
    t2.Asset_table2
FROM Table1Substrings t1
JOIN Table2Substrings t2 ON t1.Substring = t2.Substring;

方案2：匹配总共有至少3个相同字符（不要求连续） #

如果你的需求是两个字符串中相同字符的总数≥3（不管顺序和是否连续），可以用这个方法（以SQL Server为例）：

SELECT 
    t1.Assets AS Asset_table1,
    t2.Asset AS Asset_table2
FROM table1 t1
JOIN table2 t2 ON (
    -- 统计每个字符在两个字符串中的总出现次数，若某字符出现≥3次则符合条件
    (LEN(t1.Assets) + LEN(t2.Asset) - LEN(REPLACE(CONCAT(t1.Assets, t2.Asset), 'N', ''))) >= 3
    OR (LEN(t1.Assets) + LEN(t2.Asset) - LEN(REPLACE(CONCAT(t1.Assets, t2.Asset), 'G', ''))) >= 3
    OR (LEN(t1.Assets) + LEN(t2.Asset) - LEN(REPLACE(CONCAT(t1.Assets, t2.Asset), 'S', ''))) >= 3
    OR (LEN(t1.Assets) + LEN(t2.Asset) - LEN(REPLACE(CONCAT(t1.Assets, t2.Asset), 'K', ''))) >= 3
    OR (LEN(t1.Assets) + LEN(t2.Asset) - LEN(REPLACE(CONCAT(t1.Assets, t2.Asset), 'T', ''))) >= 3
    OR (LEN(t1.Assets) + LEN(t2.Asset) - LEN(REPLACE(CONCAT(t1.Assets, t2.Asset), 'L', ''))) >= 3
)
WHERE LEN(t1.Assets) >=3 AND LEN(t2.Asset)>=3;

为什么之前的例子没生效？ #

之前参考的例子大概率是针对整词匹配（比如某段完整单词在两列中出现），而你的需求是字符级别的匹配，逻辑完全不同，所以直接套用不会得到想要的结果。上面的方案是针对你的字符匹配需求专门设计的，应该能精准输出你期望的结果。

内容的提问来源于stack exchange，提问作者Joe Green