按location_code分组提取设备名称公共字符串，生成location列的SQL方案求助

这个问题我之前也处理过，刚好有个通用的方案——核心就是提取每个location_code分组下所有device_name的最长公共前缀（LCP），完美避开你提到的所有限制：不用固定长度的SUBSTRING，不用依赖统一分隔符，更不用写一大堆CASE WHEN。

具体实现（以SQL Server为例，其他数据库可适配） #

首先先模拟你的测试表和数据：

CREATE TABLE device_locations (
    location_code INT,
    device_name VARCHAR(100),
    location VARCHAR(100) -- 目标字段，初始可留空
);

INSERT INTO device_locations (location_code, device_name)
VALUES
(1, 'Building_1_in'),
(1, 'Building_1_out'),
(1, 'Building_1_gate'),
(2, 'Drive 3 gate2'),
(2, 'Drive 3 gate1'),
(2, 'Drive 3 keypad');

接下来用递归CTE来自动计算每个分组的最长公共前缀：

WITH prefix_cte AS (
    -- 初始化：每个分组取长度最短的device_name作为前缀候选（最长公共前缀不可能比最短字符串长）
    SELECT 
        location_code,
        -- 筛选出当前分组中长度最短的device_name
        (SELECT TOP 1 device_name 
         FROM device_locations sub 
         WHERE sub.location_code = main.location_code 
         ORDER BY LEN(device_name)) AS current_prefix,
        1 AS current_length
    FROM device_locations main
    GROUP BY location_code

    UNION ALL

    -- 递归扩展前缀长度，直到分组内存在不匹配的device_name
    SELECT 
        pc.location_code,
        LEFT(pc.current_prefix, pc.current_length + 1),
        pc.current_length + 1
    FROM prefix_cte pc
    WHERE 
        -- 检查当前分组所有device_name是否都包含这个长度的前缀
        (SELECT COUNT(*) 
         FROM device_locations sub 
         WHERE sub.location_code = pc.location_code 
           AND LEFT(sub.device_name, pc.current_length + 1) = LEFT(pc.current_prefix, pc.current_length + 1))
        = (SELECT COUNT(*) FROM device_locations sub WHERE sub.location_code = pc.location_code)
        -- 前缀长度不能超过候选字符串的总长度
        AND pc.current_length < LEN(pc.current_prefix)
),
-- 取每个分组中最长的有效前缀
final_locations AS (
    SELECT 
        location_code,
        MAX(current_prefix) AS location
    FROM prefix_cte
    GROUP BY location_code
)
-- 更新原表的location字段
UPDATE dl
SET dl.location = fl.location
FROM device_locations dl
JOIN final_locations fl ON dl.location_code = fl.location_code;

-- 验证结果
SELECT * FROM device_locations;

方案逻辑说明 #

1. 初始化阶段：每个分组先找到长度最短的device_name，因为最长公共前缀的长度绝对不会超过这个最短字符串，这样能减少递归的次数，提升效率。
2. 递归扩展阶段：从长度1开始，逐步增加前缀的长度，每次检查当前分组内的所有device_name是否都包含这个长度的前缀。如果全部匹配，就继续扩展；只要有一个不匹配，就停止该分组的递归。
3. 最终提取：每个分组里最长的那个有效前缀，就是我们需要的location值。

方案优势 #

• 完全自适应：不管每个分组的公共前缀长度是多少、有没有分隔符，都能自动识别。
• 无需硬编码：不管有多少个location_code分组，都不用手动写CASE WHEN，自动处理所有分组。
• 兼容性强：递归CTE在主流数据库（SQL Server、MySQL 8.0+、PostgreSQL等）都支持，只需微调语法即可适配。

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