SQL Server 2008 R2：按左侧指定长度模式匹配查询记录

首先，我先梳理清楚你的需求核心：根据给定的@Pat值，找出所有具备至少@Pat长度前缀的记录（即记录长度≥@Pat），且这些记录的前@Pat位模式在同类记录中出现次数≥2，最后返回去重后的结果。

你的原查询问题在于没有限定只统计和返回长度≥@Pat的记录，导致长度不足@Pat的记录（比如123当@Pat=10时）被错误纳入，因为它们的子串就是自身，且出现次数≥2，但这类记录并不符合示例中的预期。

正确的SQL查询 #

DECLARE @Pat int = 10; -- 可替换为3、1等测试值

WITH PatternCounts AS (
    -- 第一步：统计所有长度≥@Pat的记录的前@Pat位模式的出现次数
    SELECT 
        SUBSTRING(ColPattern, 1, @Pat) AS Pattern,
        COUNT(*) AS PatternCount
    FROM TblPatterns
    WHERE LEN(ColPattern) >= @Pat
    GROUP BY SUBSTRING(ColPattern, 1, @Pat)
    HAVING COUNT(*) > 1 -- 只保留出现次数≥2的模式
)
-- 第二步：匹配符合条件的记录并去重
SELECT DISTINCT tp.ColPattern
FROM TblPatterns tp
JOIN PatternCounts pc 
    ON SUBSTRING(tp.ColPattern, 1, @Pat) = pc.Pattern
WHERE LEN(tp.ColPattern) >= @Pat;

验证各示例 #

示例1：@Pat=10

• PatternCounts中仅会统计到模式123A456789（出现次数4次），其他长度≥10的记录的前10位模式仅出现1次，被过滤。
• 最终返回去重后的3条记录，与预期完全一致。

示例2：@Pat=3

• PatternCounts中会统计到模式123（出现7次）和243（出现2次）。
• 最终返回所有长度≥3且前3位是这两个模式的记录，去重后与预期一致。

示例3：@Pat=1

• PatternCounts中统计到模式1（出现8次）和2（出现3次），所有记录长度都≥1，因此全部被匹配返回，去重后与预期一致。

为什么原查询不符合预期？ #

你的原查询没有添加LEN(ColPattern) >= @Pat的过滤条件，导致长度小于@Pat的记录（比如123当@Pat=10时）的子串是自身，且该子串在全表中出现次数≥2，所以被错误包含进结果。而正确逻辑中，我们只关注能提供完整@Pat长度前缀的记录。

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