如何筛选包含Lookup表促销码的Main表数据并创建视图？

问题背景 #

现有两张表：

• Main表：Promotion Codes List字段为逗号分隔的促销码字符串
• Lookup表：Promotion Codes字段为单个促销码

需要创建Main表的视图，仅保留Promotion Codes List中包含Lookup表任意促销码的行。

示例数据 #

Main表样本数据

|id|Promotion Codes List|
|--| ------------------ |
|1 | BR-Coll-210731-10903, BR-NFRD-210731-10124 |
|2 | BR-Coll-210731-10903, BR-Deal-210731-11265 |
|3 | BR-Coll-210731-11037, BR-Deal-210731-11275 |
|4 | BR-Coll-210731-11037 |

Lookup表样本数据

|id|Promotion Codes|
|--| ------------- |
|1 | BR-NFRI-251231-04855 |
|2 | BR-Deal-210731-11265 |
|3 | BR-Coll-210731-11402 |
|4 | BR-Deal-210731-11275 |
|5 | BR-Coll-210731-11037 |

期望输出视图数据

|id|Promotion Codes List|
|--| ------------------ |
|2 | BR-Coll-210731-10903, BR-Deal-210731-11265 |
|3 | BR-Coll-210731-11037, BR-Deal-210731-11275 |
|4 | BR-Coll-210731-11037 |

现有问题 #

尝试的SQL仅能匹配完全一致的单行促销码，无法处理多促销码的情况：

SELECT        [Promotion Codes List], 
              CASE WHEN EXISTS (
                  SELECT *
                  FROM dbo.[Lookup] B
                  WHERE B.[Promotion Codes] = A.[Promotion Codes List]
              ) THEN 'common' ELSE 'not common' END AS valid 
FROM dbo.Main AS A

解决方案 #

提供两种可行的SQL写法：

方法1：通用模糊匹配（兼容多数数据库）

通过给字符串前后添加逗号，确保匹配完整的促销码，同时处理空格问题：

SELECT A.id, A.[Promotion Codes List]
FROM dbo.Main AS A
WHERE EXISTS (
    SELECT 1
    FROM dbo.[Lookup] B
    WHERE ',' + REPLACE(A.[Promotion Codes List], ' ', '') + ',' 
          LIKE '%,' + REPLACE(B.[Promotion Codes], ' ', '') + ',%'
)

方法2：字符串拆分精确匹配（SQL Server 2016+）

利用STRING_SPLIT拆分逗号分隔的字符串，再与Lookup表精确匹配，最后去重：

SELECT DISTINCT A.id, A.[Promotion Codes List]
FROM dbo.Main AS A
CROSS APPLY STRING_SPLIT(A.[Promotion Codes List], ',') AS SplitCodes
JOIN dbo.[Lookup] B 
    ON LTRIM(RTRIM(SplitCodes.value)) = B.[Promotion Codes]

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