SQL Server 2017中基于CSV格式字段关联表的解决方案问询

UPDATE：我完全清楚这是不良的RDBMS实践，但问题并非询问该做法是否合理，也不是如何培训创建此架构的DBA，而是寻求当前场景下的解决办法。感谢社区的帮助，这确实是个有趣的问题。

在SQL Server 2017环境下，先明确你的表结构示例（补全查找表的基础定义）：

-- 存储单个有效代码的查找表
CREATE TABLE #cd (valid_cd VARCHAR(100) PRIMARY KEY);
-- 示例有效代码数据
INSERT INTO #cd VALUES ('CD001'), ('CD002'), ('CD003');

-- 存储CSV格式代码串的交易表
CREATE TABLE #t(cd VARCHAR(100));
-- 示例交易数据
INSERT INTO #t VALUES ('CD001,CD002'), ('CD003'), ('CD004,CD001');

针对这种CSV存储的场景，我们可以利用SQL Server 2017原生支持的STRING_SPLIT函数拆分字符串，再与查找表关联处理，以下是几种常用解决方式：

方法1：匹配交易表中所有有效的代码 #

拆分CSV字符串后，关联查找表筛选出存在的有效代码：

SELECT 
    t.cd AS 原始CSV代码串,
    s.value AS 拆分后的单个代码,
    c.valid_cd AS 匹配到的有效代码
FROM #t t
CROSS APPLY STRING_SPLIT(t.cd, ',') s
LEFT JOIN #cd c ON s.value = c.valid_cd
WHERE c.valid_cd IS NOT NULL;

方法2：排查交易表中的无效代码 #

如果需要找出CSV串里不在查找表的无效代码，可以用以下语句：

SELECT 
    t.cd AS 原始CSV代码串,
    s.value AS 无效代码
FROM #t t
CROSS APPLY STRING_SPLIT(t.cd, ',') s
LEFT JOIN #cd c ON s.value = c.valid_cd
WHERE c.valid_cd IS NULL;

处理带空格的CSV串 #

如果你的CSV串存在空格（比如'CD001, CD002'），可以在拆分后去除首尾空格再关联：

SELECT 
    t.cd AS 原始CSV代码串,
    LTRIM(RTRIM(s.value)) AS 清理后的单个代码,
    c.valid_cd AS 匹配到的有效代码
FROM #t t
CROSS APPLY STRING_SPLIT(t.cd, ',') s
LEFT JOIN #cd c ON LTRIM(RTRIM(s.value)) = c.valid_cd
WHERE c.valid_cd IS NOT NULL;

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