SQL 服务器 : how do I find the most common string in a field?

Question

我有一个包含 1,000,000 多条记录的表，我想找到长度至少为 5 个字符的最常见的子字符串。

如果我有以下条目:

KDHFOUDHGOENWFIJ 1114H4363SDFHDHGFDG
GSDLGJSLJSKJDFSG 1114H20SDGDSSFHGSLD
SLSJDHLJKSSDJFKD 1114HJSDHFJKSDKFSGG

我想在 SQL 中编写一条语句，选择 1114H 作为最常见的子字符串。我该怎么做？

注意事项:

• 子字符串不必位于同一位置。
• 子串的长度必须为 5
• 每条记录的最大长度为50个字符

Answer 1

没有找到最长子串的要求，因此每个长度大于 5 的子串总会有一个 5 个字符的子串，这与计数相同。所以我们只需要检查长度为 5 的子串。

在示例数据中，有三个字符串出现了三次。 _1114H, _1114 and 1114H (_是表示空格的位置).

在此解决方案中，master..spt_values 用于代替数字表。

declare @T table
(
  ID int identity,
  Data varchar(50)
)

insert into @T values
('KDHFOUDHGOENWFIJ 1114H4363SDFHDHGFDG'),
('GSDLGJSLJSKJDFSG 1114H20SDGDSSFHGSLD'),
('SLSJDHLJKSSDJFKD 1114HJSDHFJKSDKFSGG')

select top 1 substring(T.Data, N.Number, 5) as Word
from @T as T
  cross apply (select N.Number
               from master..spt_values as N
               where N.type = 'P' and
                     N.number between 1 and len(T.Data)-4) as N
group by substring(T.Data, N.Number, 5)                      
order by count(distinct id) desc

结果:

Word
------
 1114