SQL Server 2016中去除路由相邻重复城市代码的实现需求

需求 #

将同一ID下按LNO排序的路由合并后，去除由短横线(-)分隔的相邻重复城市代码（注：源数据中存在下划线分隔的路由段，需保留其格式）。

源数据 #

ID  ROUTE   LNO
1   JFK-LAX 1
1   LAX_IAD 2
1   IAD-ORD 3
5   MIA-ATL 1
5   ATL-MIA 2
2   SEA-IAH 1
2   IAH-AUS 2
2   AUS-SEA 3

当前查询语句 #

SELECT ID, STUFF((
    SELECT '-' + ROUTE
    FROM DST
    WHERE ID = d.ID
    ORDER BY LNO
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Route
FROM DST d
GROUP BY ID;

当前查询结果 #

ID  Route
1   JFK-LAX-LAX_IAD-IAD-ORD
2   SEA-IAH-IAH-AUS-AUS-SEA
5   MIA-ATL-ATL-MIA

期望结果 #

ID  Route
1   JFK-LAX_IAD-ORD
2   SEA-IAH-AUS-SEA
5   MIA-ATL-MIA

解决方案 #

通过CTE拆分路由段的首尾部分，结合窗口函数LAG判断相邻路由的重复部分，过滤后再合并：

WITH RouteParts AS (
    SELECT 
        ID,
        ROUTE,
        LNO,
        -- 提取路由段的起始部分（兼容-和_两种分隔符）
        LEFT(ROUTE, CHARINDEX(CASE WHEN CHARINDEX('-', ROUTE) > 0 THEN '-' ELSE '_' END, ROUTE)-1) AS StartPart,
        -- 提取路由段的结束部分
        RIGHT(ROUTE, LEN(ROUTE)-CHARINDEX(CASE WHEN CHARINDEX('-', ROUTE) > 0 THEN '-' ELSE '_' END, ROUTE)) AS EndPart,
        ROUTE AS FullRoute
    FROM DST
),
FilteredRoutes AS (
    SELECT 
        ID,
        LNO,
        -- 若当前路由段的起始部分与前一段的结束部分重复，则只保留当前段的后半部分
        CASE 
            WHEN LAG(EndPart) OVER (PARTITION BY ID ORDER BY LNO) = StartPart 
            THEN RIGHT(FullRoute, LEN(FullRoute)-LEN(StartPart)-1)
            ELSE FullRoute
        END AS CleanedRoute
    FROM RouteParts
)
SELECT 
    ID,
    STUFF((
        SELECT '-' + CleanedRoute
        FROM FilteredRoutes fr
        WHERE fr.ID = d.ID
        ORDER BY fr.LNO
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Route
FROM FilteredRoutes d
GROUP BY ID;

逻辑说明 #

1. RouteParts CTE：拆分每个路由段的起始和结束部分，自动适配-和_两种分隔符；
2. FilteredRoutes CTE：使用LAG函数获取前一个路由段的结束部分，与当前段的起始部分对比，若重复则截断当前段的重复前缀；
3. 最后通过FOR XML PATH合并处理后的路由段，得到去重后的完整路由。

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