如何优化多缩写组合的SQL查询，实现合并描述输出

问题背景 #

现有一张存储项目ID的数据表Projects，其中SpecialConditions字段为varchar类型，存储项目相关的缩写（支持多缩写组合）。需要将该字段的缩写转换为对应描述文本并合并为易读格式。原方案使用CASE语句实现，但因缩写组合存在多种排列顺序，需频繁修改查询语句，需更优方案。

原始数据 #

| ProjectID | SpecialConditions |
| --------  | ----------------- |
| 2023-0001 |        00         |
| 2023-0002 |        CBSP       |
| 2023-0003 |        KCB        |
| 2023-0004 |                   |
| 2023-0005 |        K          |
| 2023-0006 |        WMCBSP     |

缩写映射规则 #

00 = No Fruits
CB = Cranberry
K  = Kiwi
SP = Sugar Plum
WM = Watermelon

期望输出 #

| ProjectID | SpecialConditions                 |
| --------  | --------------------------------- |
| 2023-0001 | No Fruits                         |
| 2023-0002 | Cranberry, Sugar Plum             |
| 2023-0003 | Kiwi, Cranberry                   |
| 2023-0004 |                                   |
| 2023-0005 | Kiwi                              |
| 2023-0006 | Watermelon, Cranberry, Sugar Plum |

原CASE语句实现（存在缺陷） #

SELECT 
    CASE 
        WHEN SpecialConditions = '00' then 'No Fruits'
        WHEN SpecialConditions = 'CB' then 'Cranberry'
        WHEN SpecialConditions = 'K' then 'Kiwi'
        WHEN SpecialConditions = 'SP' then 'Sugar Plum'
        WHEN SpecialConditions = 'WM' then 'Watermelon'
        WHEN SpecialConditions = 'SPWM' then 'Sugar Plum, Watermelon'
        WHEN SpecialConditions = 'WMSP' then 'Sugar Plum, Watermelon'
        ELSE COALESCE(SpecialConditions, '') 
    END as 'Fruits'
FROM Projects

最优解决方案 #

1. 创建缩写映射表 #

将缩写与描述的映射关系持久化到数据库表中，后续维护只需更新此表，无需修改查询语句：

CREATE TABLE AbbreviationMap (
    Abbreviation VARCHAR(10) PRIMARY KEY,
    Description VARCHAR(100) NOT NULL
);

INSERT INTO AbbreviationMap (Abbreviation, Description)
VALUES 
('00', 'No Fruits'),
('CB', 'Cranberry'),
('K', 'Kiwi'),
('SP', 'Sugar Plum'),
('WM', 'Watermelon');

2. 核心查询语句 #

SQL Server 版本

利用STRING_AGG函数合并匹配到的描述，按缩写在原字符串中的顺序排序：

SELECT 
    p.ProjectID,
    CASE 
        WHEN p.SpecialConditions = '00' THEN am.Description
        WHEN p.SpecialConditions IS NULL OR p.SpecialConditions = '' THEN ''
        ELSE STRING_AGG(am.Description, ', ') WITHIN GROUP (ORDER BY CHARINDEX(am.Abbreviation, p.SpecialConditions))
    END AS SpecialConditions
FROM Projects p
LEFT JOIN AbbreviationMap am 
    ON p.SpecialConditions != '00' AND CHARINDEX(am.Abbreviation, p.SpecialConditions) > 0
GROUP BY p.ProjectID, p.SpecialConditions;

MySQL 版本

使用GROUP_CONCAT函数替代STRING_AGG：

SELECT 
    p.ProjectID,
    CASE 
        WHEN p.SpecialConditions = '00' THEN am.Description
        WHEN p.SpecialConditions IS NULL OR p.SpecialConditions = '' THEN ''
        ELSE GROUP_CONCAT(am.Description ORDER BY LOCATE(am.Abbreviation, p.SpecialConditions) SEPARATOR ', ')
    END AS SpecialConditions
FROM Projects p
LEFT JOIN AbbreviationMap am 
    ON p.SpecialConditions != '00' AND LOCATE(am.Abbreviation, p.SpecialConditions) > 0
GROUP BY p.ProjectID, p.SpecialConditions;

方案优势 #

• 可维护性强：新增、修改或删除缩写只需更新AbbreviationMap表，无需调整查询逻辑
• 自动适配组合顺序：无论缩写组合的排列顺序如何，都能准确匹配并转换
• 处理边界情况：兼容空值、空字符串及00特殊值的场景
• 输出有序：保持描述与原字符串中缩写的出现顺序一致

注意事项 #

• 确保映射表中的缩写无重叠（例如避免同时存在C和CB这类包含关系的缩写，否则会导致匹配错误）
• 00作为独立标识单独处理，不与其他水果缩写组合匹配

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