SQL中对比逗号分隔字符串：关联公司变动分析需求及代码问题

临时表定义与数据插入 #

首先创建临时表#COMPANY_CHANGE并插入测试数据：

CREATE TABLE #COMPANY_CHANGE 
(       
    Risk_ID VARCHAR(50),
    LINKED_COMPANIES VARCHAR(MAX),
    PERIOD VARCHAR(20)
)

INSERT INTO #COMPANY_CHANGE (Risk_ID, LINKED_COMPANIES, PERIOD)
VALUES
    (1, 'X,y,z', '202202'),
    (1, 'X,y', '202208'),
    (2, 'A,B,C', '202202'),
    (2, 'B,C,D', '202208'),
    (4, 'Z', '202202'),
    (4, 'Z', '202208') 

需求描述 #

针对每个Risk_ID，对比起始周期202202与结束周期202208的LINKED_COMPANIES字段，识别出新增和移除的公司，最终输出每个Risk_ID在202208周期的变更状态：

• 标记是否有变更（Yes/No）
• 列出新增的公司（无则显示Null）
• 列出移除的公司（无则显示Null）

期望输出 #

Risk_ID  PERIOD   Change  Company_Added Company_Removed
1        202208   Yes     Null          z
2        202208   Yes     D             A
4        202208   No      Null          Null

当前代码问题 #

以下是用户编写的代码，无法正确识别新增/移除的公司：

WITH CompanyChanges AS (
  SELECT
    RISK_ID,
    PERIOD,
    LINKED_COMPANIES,
    LAG(LINKED_COMPANIES) OVER (PARTITION BY RISK_ID ORDER BY PERIOD) AS PREVIOUS_LINKED_COMPANIES
  FROM COMPANY_CHANGE
)
SELECT
  RISK_ID,
  PERIOD,
  STRING_AGG(REPLACE(LINKED_COMPANIES, PREVIOUS_LINKED_COMPANIES, ''), ',') AS Change
FROM CompanyChanges
WHERE PREVIOUS_LINKED_COMPANIES IS NOT NULL AND PREVIOUS_LINKED_COMPANIES <> LINKED_COMPANIES
GROUP BY RISK_ID, PERIOD
ORDER BY RISK_ID, PERIOD;

问题分析：直接对字符串进行替换或对比无法准确识别集合的差异（比如字符串顺序变化、部分重叠的情况），必须先将逗号分隔的字符串拆分为单个公司的集合，再进行集合对比。

解决方案 #

实现思路 #

1. 将每个周期的LINKED_COMPANIES拆分为单个公司的行数据
2. 分别提取每个Risk_ID在202202和202208的公司集合
3. 对比两个集合，筛选出仅存在于202208的新增公司、仅存在于202202的移除公司
4. 聚合结果，生成符合要求的输出格式

完整SQL代码 #

WITH SplitCompanies AS (
    -- 拆分逗号分隔的公司字符串为单个行
    SELECT 
        Risk_ID,
        PERIOD,
        TRIM(value) AS Company
    FROM #COMPANY_CHANGE
    CROSS APPLY STRING_SPLIT(LINKED_COMPANIES, ',')
),
PeriodComparison AS (
    -- 标记每个公司在两个周期的存在情况
    SELECT
        Risk_ID,
        Company,
        CASE WHEN PERIOD = '202202' THEN 1 ELSE 0 END AS Exists_In_Start,
        CASE WHEN PERIOD = '202208' THEN 1 ELSE 0 END AS Exists_In_End
    FROM SplitCompanies
),
ChangeDetails AS (
    -- 聚合每个Risk_ID的新增/移除公司
    SELECT
        Risk_ID,
        STRING_AGG(CASE WHEN Exists_In_Start = 0 AND Exists_In_End = 1 THEN Company END, ',') AS Company_Added,
        STRING_AGG(CASE WHEN Exists_In_Start = 1 AND Exists_In_End = 0 THEN Company END, ',') AS Company_Removed
    FROM PeriodComparison
    GROUP BY Risk_ID
)
-- 生成最终输出
SELECT
    Risk_ID,
    '202208' AS PERIOD,
    CASE 
        WHEN Company_Added IS NOT NULL OR Company_Removed IS NOT NULL THEN 'Yes' 
        ELSE 'No' 
    END AS Change,
    ISNULL(Company_Added, 'Null') AS Company_Added,
    ISNULL(Company_Removed, 'Null') AS Company_Removed
FROM ChangeDetails
ORDER BY Risk_ID;

代码说明 #

• STRING_SPLIT：用于拆分逗号分隔的字符串（SQL Server 2016及以上版本支持；若使用旧版本，可自行替换为自定义字符串拆分函数）
• TRIM：去除公司名称前后的空格，避免因空格导致的对比错误
• 集合对比：通过标记公司在两个周期的存在状态，准确筛选新增和移除的公司
• STRING_AGG：将多个新增/移除的公司合并为逗号分隔的字符串，符合原数据格式

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