能否使用SQL FREETEXTTABLE比较表列值而非显式输入值识别相似账户？

问题描述 #

需要识别因名称相似可能重复创建的客户账户（例如“Smith's stores”与“Smith Store”），希望对同表内其他记录执行自由文本相似匹配，而非使用固定的显式输入值。当前用显式值的SQL语句能正常运行，但把输入值换成表的列名就会报错，现有可正常运行的语句如下：

SELECT key_tbl.[rank],  * FROM Accounts a
JOIN FREETEXTTABLE (Accounts, AccountName, 'Smith store')  as key_tbl
on key_tbl.[key] = a.DW_id
ORDER BY key_tbl.[RANK] DESC

解决方案 #

FREETEXTTABLE的第三个参数不支持直接传列名，它要求是静态的自由文本查询字符串。要实现同表内每条记录和其他记录的自由文本匹配，可采用自结合加CROSS APPLY的方式逐行处理：

SELECT 
    a.DW_id AS original_id,
    a.AccountName AS original_name,
    key_tbl.[rank],
    match.DW_id AS match_id,
    match.AccountName AS match_name
FROM Accounts a
CROSS APPLY FREETEXTTABLE(Accounts, AccountName, a.AccountName) AS key_tbl
JOIN Accounts match ON key_tbl.[key] = match.DW_id
-- 排除自身匹配，避免把自己当成重复项
WHERE a.DW_id <> match.DW_id
ORDER BY a.DW_id, key_tbl.[rank] DESC

补充说明 #

• 用CROSS APPLY可以让FREETEXTTABLE逐个读取a.AccountName的值作为查询词，实现每条记录和表内其他记录的自由文本匹配。
• 添加WHERE a.DW_id <> match.DW_id是为了过滤掉账户自身匹配的结果，只保留不同账户间的相似匹配。
• 如果需要控制匹配的相似度阈值，可以根据key_tbl.[rank]的值筛选，比如添加AND key_tbl.[rank] > 50（具体阈值需根据实际数据调整）。

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