如何提取数据集项目名中不含所有者姓名的最短子串？

嘿，针对你这个百万级数据集的需求，我分两种最常见的匹配场景来给你方案，还有适合大数据的优化技巧：

场景1：字符级匹配（排除所有者姓名里的任意单个字符） #

如果你的需求是找完全不包含所有者姓名中任何一个字符的最短连续子串（比如所有者是"A B C"，那子串里不能有A、B、C，空格要不要排除可以自己调整），可以用下面的高效SQL来处理——我特意避开了生成所有子串的笨方法，而是先定位有效字符段，效率更高：

示例代码 #

WITH CharRanges AS (
    -- 拆分项目名的每个字符，标记是否属于所有者姓名的字符（先去掉所有者里的空格）
    SELECT 
        t.ID,
        t.OWNER,
        t.ITEM,
        v.number AS Pos,
        -- 判断当前字符是否不在所有者姓名的有效字符里（这里去掉了所有者的空格，若要包含空格就删掉REPLACE）
        CASE WHEN CHARINDEX(SUBSTRING(t.ITEM, v.number, 1), REPLACE(t.OWNER, ' ', '')) = 0 THEN 1 ELSE 0 END AS IsValidChar
    FROM test t
    JOIN master..spt_values v ON v.number BETWEEN 1 AND LEN(t.ITEM)
    WHERE v.type = 'P'
),
ValidSegments AS (
    -- 把连续的有效字符归为同一个分组
    SELECT 
        ID,
        OWNER,
        ITEM,
        Pos,
        IsValidChar,
        SUM(CASE WHEN IsValidChar = 1 THEN 0 ELSE 1 END) OVER(PARTITION BY ID ORDER BY Pos) AS SegmentGroup
    FROM CharRanges
),
SegmentDetails AS (
    -- 计算每个有效字符段的起始位置、长度
    SELECT 
        ID,
        OWNER,
        ITEM,
        MIN(Pos) AS StartPos,
        MAX(Pos) AS EndPos,
        MAX(Pos) - MIN(Pos) + 1 AS Length
    FROM ValidSegments
    WHERE IsValidChar = 1
    GROUP BY ID, OWNER, ITEM, SegmentGroup
)
-- 按ID取最短的有效子串
SELECT 
    ID,
    OWNER,
    ITEM,
    SUBSTRING(ITEM, StartPos, Length) AS Shortest_Substring
FROM SegmentDetails
WHERE Length = (SELECT MIN(Length) FROM SegmentDetails WHERE ID = SegmentDetails.ID);

对你的示例数据的结果 #

拿ID=1的记录来说，所有者是"A B C"，项目名是"A B X X X"，有效字符是X，所以最短子串就是单个X。

场景2：单词级匹配（排除所有者姓名里的任意单词） #

如果你的需求是按单词来匹配（比如所有者姓名拆成"A"、"B"、"C"三个单词，要找项目名里完全不包含这些单词的最短子串），可以用下面的方案：

示例代码 #

WITH SplitOwnerWords AS (
    -- 把所有者姓名拆成单个单词，去掉空值
    SELECT 
        ID,
        OWNER,
        ITEM,
        TRIM(value) AS OwnerWord
    FROM test
    CROSS APPLY STRING_SPLIT(OWNER, ' ')
    WHERE TRIM(value) <> ''
),
SplitItemWords AS (
    -- 把项目名拆成单个单词，记录每个单词的位置
    SELECT 
        ID,
        OWNER,
        ITEM,
        TRIM(value) AS ItemWord,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT NULL)) AS WordPos
    FROM test
    CROSS APPLY STRING_SPLIT(ITEM, ' ')
    WHERE TRIM(value) <> ''
),
WordSegments AS (
    -- 生成所有连续的单词子串
    SELECT 
        ID,
        OWNER,
        ITEM,
        ItemWord AS Substring,
        WordPos AS StartPos,
        WordPos AS EndPos,
        1 AS WordCount
    FROM SplitItemWords
    UNION ALL
    SELECT 
        s.ID,
        s.OWNER,
        s.ITEM,
        ws.Substring + ' ' + s.ItemWord,
        ws.StartPos,
        s.WordPos,
        ws.WordCount + 1
    FROM SplitItemWords s
    JOIN WordSegments ws ON s.ID = ws.ID AND s.WordPos = ws.EndPos + 1
),
ValidSegments AS (
    -- 筛选出不包含任何所有者单词的子串
    SELECT 
        ID,
        OWNER,
        ITEM,
        Substring,
        WordCount
    FROM WordSegments
    WHERE NOT EXISTS (
        SELECT 1
        FROM SplitOwnerWords ow
        WHERE ow.ID = WordSegments.ID AND CHARINDEX(ow.OwnerWord, WordSegments.Substring) > 0
    )
)
-- 按ID取单词数最少的子串
SELECT 
    ID,
    OWNER,
    ITEM,
    MIN(Substring) AS Shortest_Substring
FROM ValidSegments
GROUP BY ID, OWNER, ITEM
HAVING MIN(WordCount) = (SELECT MIN(WordCount) FROM ValidSegments WHERE ID = ValidSegments.ID);

对你的示例数据的结果 #

同样拿ID=1的记录，项目名里的单词"A"、"B"属于所有者，剩下的"X"是有效单词，所以最短子串就是X。

针对百万级数据的优化提示 #

如果你的数据集真的有数百万行，上面的单词级方案可能会有点慢，因为生成所有连续单词子串会消耗资源。这里给你两个优化方向：

• 先预处理项目名，直接标记出连续的、不包含所有者单词的段落，然后取最短的那段，不用生成所有子串。
• 如果用的是SQL Server 2019+，可以试试用STRING_AGG结合窗口函数来更高效地定位有效段落，避免递归CTE的开销。

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