如何高效编写匹配百余个诊断码前缀的SQL查询？

1. 临时表+JOIN匹配（推荐高频复用场景） #

当前缀数量超过200个时，把所有前缀存入临时表，通过JOIN关联诊断表，比一堆OR或IN子句性能更稳定，还方便维护前缀列表。

示例SQL：

-- 创建临时前缀表（前缀长度根据实际情况调整，这里按3位示例）
CREATE TEMPORARY TABLE Prefixes (prefix VARCHAR(3) PRIMARY KEY);
-- 批量插入所有前缀
INSERT INTO Prefixes VALUES ('A01'), ('B05'), ('C10'), ...; -- 200+个前缀

-- 查询匹配的诊断记录
SELECT d.Diagnosis_Cd, d.Description
FROM DiagnosisTable d
-- 如果前缀长度统一，用LEFT截取匹配；长度不统一就用LIKE拼接
JOIN Prefixes p ON LEFT(d.Diagnosis_Cd, 3) = p.prefix;

如果前缀长度不一致，把JOIN条件改成d.Diagnosis_Cd LIKE CONCAT(p.prefix, '%')即可。

2. UNION ALL替代多OR（一次性查询场景） #

把原来的多个OR LIKE拆成多个独立查询，用UNION ALL拼接结果。数据库对UNION ALL的优化远优于超长OR链，尤其是Diagnosis_Cd建了前缀索引时，每个子查询都能命中索引。

示例SQL：

SELECT Diagnosis_Cd, Description FROM DiagnosisTable WHERE Diagnosis_Cd LIKE 'A01%'
UNION ALL
SELECT Diagnosis_Cd, Description FROM DiagnosisTable WHERE Diagnosis_Cd LIKE 'B05%'
UNION ALL
SELECT Diagnosis_Cd, Description FROM DiagnosisTable WHERE Diagnosis_Cd LIKE 'C10%'
... -- 依次添加所有前缀的查询语句

如果担心结果有重复，把UNION ALL换成UNION去重，不过后者性能稍差，按需选择。

3. 优化你的CTE方案 #

你当前用CTE截取前缀后IN的方式，会导致Diagnosis_Cd的索引失效（因为用了LEFT函数），可以把CTE改成存储前缀列表，再通过LIKE关联，部分数据库能优化这种匹配逻辑，利用前缀索引。

示例SQL：

WITH Prefixes AS (
    SELECT 'A01' AS prefix UNION ALL
    SELECT 'B05' UNION ALL
    SELECT 'C10' UNION ALL
    ... -- 列出所有前缀
)
SELECT d.Diagnosis_Cd, d.Description
FROM DiagnosisTable d
JOIN Prefixes p ON d.Diagnosis_Cd LIKE CONCAT(p.prefix, '%');

额外优化建议 #

给Diagnosis_Cd字段建前缀索引，比如针对3位前缀的索引：

CREATE INDEX idx_diagnosis_cd_prefix ON DiagnosisTable(Diagnosis_Cd(3));

不管用哪种方案，前缀索引都能大幅缩短查询时间。

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