如何使用CONCAT_WS和COALESCE还原MySQL中逗号分隔的医学并发症编码

先说明：我清楚用逗号分隔存储字符串值在MySQL里一直是个颇具争议的设计，但咱们今天不纠结这个设计的优劣，专注解决你的实际需求——把存储的并发症编码（逗号分隔字符串）转成对应的医学含义，用CONCAT_WS和COALESCE来实现。

先设定场景 #

假设我们有两个核心表：

• complication_lookup：并发症编码字典表，存储编码与对应的医学含义
• patient_surgeries：患者手术记录表，其中complication_codes字段用逗号分隔存储并发症编码（比如C1,C3）

先创建示例表和测试数据：

-- 并发症字典表
CREATE TABLE complication_lookup (
    code VARCHAR(10) PRIMARY KEY,
    description VARCHAR(100) NOT NULL
);

INSERT INTO complication_lookup VALUES
('C1', '术后出血'),
('C2', '手术部位感染'),
('C3', '邻近器官损伤'),
('C4', '深静脉血栓形成');

-- 患者手术记录表
CREATE TABLE patient_surgeries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_name VARCHAR(50),
    complication_codes VARCHAR(50)
);

INSERT INTO patient_surgeries VALUES
(1, '张三', 'C1,C3'),
(2, '李四', 'C2,C5'), -- C5是未在字典中定义的编码
(3, '王五', NULL); -- 无并发症记录

核心实现思路 #

要完成编码转含义的转换，需要三步：

1. 把逗号分隔的编码字符串拆分成单独的编码行
2. 关联字典表，获取每个编码对应的医学含义
3. 用COALESCE处理无匹配的编码，再用CONCAT_WS把结果拼接回字符串

针对MySQL 8.0+的实现（支持STRING_SPLIT） #

MySQL 8.0及以上版本提供了STRING_SPLIT函数，拆分字符串非常便捷：

SELECT
    ps.id,
    ps.patient_name,
    CONCAT_WS(', ', 
        COALESCE(cl.description, split_codes.code)
    ) AS complication_descriptions
FROM patient_surgeries ps
LEFT JOIN (
    -- 拆分逗号分隔的编码为单独行，同时去掉编码前后可能的空格
    SELECT 
        ps_inner.id,
        TRIM(split_code.value) AS code
    FROM patient_surgeries ps_inner
    JOIN STRING_SPLIT(ps_inner.complication_codes, ',') split_code
    WHERE ps_inner.complication_codes IS NOT NULL
) split_codes ON ps.id = split_codes.id
LEFT JOIN complication_lookup cl ON split_codes.code = cl.code
GROUP BY ps.id, ps.patient_name
-- 单独处理无并发症的情况
UNION ALL
SELECT
    id,
    patient_name,
    '无并发症' AS complication_descriptions
FROM patient_surgeries
WHERE complication_codes IS NULL;

针对低版本MySQL（无STRING_SPLIT）的实现 #

如果你的MySQL版本低于8.0，可以用递归CTE来拆分字符串：

WITH RECURSIVE split_codes AS (
    -- 初始化：提取第一个编码
    SELECT
        id,
        patient_name,
        complication_codes,
        1 AS n,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(complication_codes, ',', n), ',', -1)) AS code
    FROM patient_surgeries
    WHERE complication_codes IS NOT NULL
    UNION ALL
    -- 递归：依次提取后续的编码
    SELECT
        id,
        patient_name,
        complication_codes,
        n + 1 AS n,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(complication_codes, ',', n + 1), ',', -1)) AS code
    FROM split_codes
    WHERE n < LENGTH(complication_codes) - LENGTH(REPLACE(complication_codes, ',', '')) + 1
)
SELECT
    id,
    patient_name,
    CONCAT_WS(', ', 
        COALESCE(cl.description, code)
    ) AS complication_descriptions
FROM split_codes
LEFT JOIN complication_lookup cl ON split_codes.code = cl.code
GROUP BY id, patient_name
-- 单独处理无并发症的情况
UNION ALL
SELECT
    id,
    patient_name,
    '无并发症' AS complication_descriptions
FROM patient_surgeries
WHERE complication_codes IS NULL;

关键函数说明 #

• COALESCE(cl.description, code)：如果编码在字典表中有匹配的描述，就用描述；如果没有（比如例子里的C5），就保留原编码，避免显示NULL
• CONCAT_WS(', ', ...)：用逗号加空格拼接多个结果，自动忽略NULL值，不会出现多余的分隔符（比如不会因为某个编码无匹配而拼接出, ,这样的错误格式）
• TRIM()：处理编码前后可能存在的空格，避免因为空格导致关联字典表失败

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