如何从多行相似值中提取最长公共前缀子串并分组

• 无需预先指定子串，从多行数据中提取相同子串，本质是找出2-5个词的公共部分
• 核心目标：找到多行数据的最长公共基准串作为分组依据，修剪每组中单行独有的末尾子串
• 分组规则：左前缀相同的行归为同一组，例如：
  • "Item A 1"与"Item A 2"属于"Item A"组
  • "Item A A 1"与"Item A A 2"属于"Item A A"组
  • 若某行本身就是组名（如"Item A"），则与"Item A 1"同属"Item A"组

CREATE TABLE #temp (
    id INT,
    item NVARCHAR(50)
);

INSERT INTO #temp (id, item) VALUES
(1,'DEP Item 1'),
(2,'DEP Item 10'),
(3,'DEP Item 11'),
(4,'DEP Item 24'),
(5,'DEP G1 Item B 1'),
(6,'DEP G1 Item B 10'),
(7,'DEP G1 Item B 11'),
(8,'DEP G1 Item B 24'),
(9,'DEP A Item B 1 1 A'),
(10,'DEP A Item B 1 10'),
(11,'DEP A Item B 1 11 B'),
(12,'DEP A Item B 1 24 A B C')

select *,
CASE WHEN LEN(item)-LEN(REPLACE(item, ' ', '')) < 1 THEN item
    ELSE LEFT(item, CHARINDEX(' ', item,
        CHARINDEX(' ', item)+1))
    end,
CASE WHEN LEN(item)-LEN(REPLACE(item, ' ', '')) < 2 THEN item
    ELSE LEFT(item, CHARINDEX(' ', item,
        CHARINDEX(' ', item,
        CHARINDEX(' ', item)+1)+1))
    end,
CASE WHEN LEN(item)-LEN(REPLACE(item, ' ', '')) < 4 THEN item
    ELSE LEFT(item, CHARINDEX(' ', item,
        CHARINDEX(' ', item,
        CHARINDEX(' ', item,
        CHARINDEX(' ', item)+1)+1)+1))
    end,
CASE WHEN LEN(item)-LEN(REPLACE(item, ' ', '')) < 5 THEN item
    ELSE LEFT(item, CHARINDEX(' ', item,
        CHARINDEX(' ', item,
        CHARINDEX(' ', item,
        CHARINDEX(' ', item,
        CHARINDEX(' ', item)+1)+1)+1)+1))
    end
from #temp

要实现动态提取最长公共前缀作为分组基准，核心思路是先拆分所有item的词段，再逐层验证前缀的共有性，最终筛选出符合2-5词长度的最长公共基准串。以下是可行的SQL实现：

WITH SplitItems AS (
    -- 拆分item为单个词段，记录每个词的位置
    SELECT 
        id,
        item,
        value AS word,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY CHARINDEX(' ' + value + ' ', ' ' + item + ' ')) AS word_pos
    FROM #temp
    CROSS APPLY STRING_SPLIT(item, ' ')
),
PrefixCandidates AS (
    -- 生成所有可能的前缀（2-5词长度），并统计每个前缀对应的行数
    SELECT 
        STRING_AGG(word, ' ') WITHIN GROUP (ORDER BY word_pos) AS prefix,
        COUNT(DISTINCT id) AS row_count,
        MAX(word_pos) AS prefix_length
    FROM SplitItems
    GROUP BY id
    CROSS APPLY (
        SELECT word, word_pos
        FROM SplitItems si
        WHERE si.id = SplitItems.id AND si.word_pos <= 5
    ) AS sub
    GROUP BY 
        (SELECT STRING_AGG(word, ' ') WITHIN GROUP (ORDER BY word_pos) FROM SplitItems si WHERE si.id = SplitItems.id AND si.word_pos <= 2),
        (SELECT STRING_AGG(word, ' ') WITHIN GROUP (ORDER BY word_pos) FROM SplitItems si WHERE si.id = SplitItems.id AND si.word_pos <= 3),
        (SELECT STRING_AGG(word, ' ') WITHIN GROUP (ORDER BY word_pos) FROM SplitItems si WHERE si.id = SplitItems.id AND si.word_pos <= 4),
        (SELECT STRING_AGG(word, ' ') WITHIN GROUP (ORDER BY word_pos) FROM SplitItems si WHERE si.id = SplitItems.id AND si.word_pos <= 5)
    HAVING MAX(word_pos) BETWEEN 2 AND 5
),
GroupBase AS (
    -- 筛选每个分组的最长前缀
    SELECT 
        prefix,
        row_count
    FROM PrefixCandidates pc
    WHERE NOT EXISTS (
        SELECT 1 FROM PrefixCandidates pc2
        WHERE pc2.row_count = pc.row_count 
          AND pc2.prefix LIKE pc.prefix + ' %'
          AND pc2.prefix_length > pc.prefix_length
    )
)
-- 关联原表得到最终分组结果
SELECT 
    t.id,
    t.item,
    gb.prefix AS [set]
FROM #temp t
JOIN GroupBase gb ON t.item LIKE gb.prefix + '%'
   OR t.item = gb.prefix
ORDER BY t.id;

方案说明 #

1. SplitItems：将每个item拆分成独立词段，并标记每个词的位置，方便后续生成前缀。
2. PrefixCandidates：生成所有2-5词长度的前缀候选，统计每个前缀覆盖的行数，确保只保留符合长度要求的候选。
3. GroupBase：在候选前缀中筛选出最长的那个（排除被更长前缀包含的短前缀），作为最终分组基准。
4. 最终关联：将原表数据与分组基准关联，匹配规则包含"item是前缀本身"或"item以前缀开头"，得到预期结果。

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