SQL中REPLACE函数匹配唯一值失效问题求助

你的问题出在嵌套REPLACE的执行顺序：短数字编码（如'1'）先被替换后，会破坏长编码（如'11'）的结构，导致长编码无法被正确匹配，最终返回混乱结果。以下是几种可行的解决办法：

方法1：使用CASE WHEN精确匹配（推荐） #

这是最稳妥的方案，通过精确匹配字段的完整值来替换，不会出现部分替换的问题：

CASE 
    WHEN CFCFIncidentRootCause = '0' THEN 'No Root Cause'
    WHEN CFCFIncidentRootCause = '1' THEN 'Data Issue'
    WHEN CFCFIncidentRootCause = '2' THEN 'Hardware Fault'
    WHEN CFCFIncidentRootCause = '3' THEN 'Software Issue'
    WHEN CFCFIncidentRootCause = '4' THEN 'Change Failure'
    WHEN CFCFIncidentRootCause = '5' THEN 'Permissions'
    WHEN CFCFIncidentRootCause = '6' THEN 'Third Party Issue'
    WHEN CFCFIncidentRootCause = '7' THEN 'User Error Training'
    WHEN CFCFIncidentRootCause = '8' THEN 'Network Issue'
    WHEN CFCFIncidentRootCause = '10' THEN 'Performance Issue'
    WHEN CFCFIncidentRootCause = '11' THEN 'Non IT Issue'
    WHEN CFCFIncidentRootCause = '12' THEN 'Unknown Root Cause'
    WHEN CFCFIncidentRootCause = '13' THEN 'Request Fulfilled'
    WHEN CFCFIncidentRootCause = '14' THEN 'Request Denied'
    WHEN CFCFIncidentRootCause = '15' THEN 'Security Issue'
    ELSE CFCFIncidentRootCause -- 保留无法匹配的原始值
END AS CFCFIncidentRootCause

方法2：调整REPLACE顺序（仅适用于必须用REPLACE的场景） #

如果系统限制只能使用REPLACE函数，需先替换长编码，再替换短编码，避免长编码被提前破坏：

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CFCFIncidentRootCause,
'15', 'Security Issue'),
'14', 'Request Denied'),
'13', 'Request Fulfilled'),
'12', 'Unknown Root Cause'),
'11', 'Non IT Issue'),
'10', 'Performance Issue'),
'0', 'No Root Cause'),
'2', 'Hardware Fault'),
'1', 'Data Issue'),
'4', 'Change Failure'),
'6', 'Third Party Issue'),
'7', 'User Error Training'),
'8', 'Network Issue'),
'3', 'Software Issue'),
'5', 'Permissions')
AS CFCFIncidentRootCause

方法3：创建映射表关联查询（适合编码较多或需频繁更新的场景） #

先建立根因编码映射表，再通过关联查询完成替换：

1. 创建映射表：

CREATE TABLE RootCauseMapping (
    Code VARCHAR(2) PRIMARY KEY,
    RootCauseName VARCHAR(50) NOT NULL
);

INSERT INTO RootCauseMapping (Code, RootCauseName)
VALUES 
('0', 'No Root Cause'),
('1', 'Data Issue'),
('2', 'Hardware Fault'),
('3', 'Software Issue'),
('4', 'Change Failure'),
('5', 'Permissions'),
('6', 'Third Party Issue'),
('7', 'User Error Training'),
('8', 'Network Issue'),
('10', 'Performance Issue'),
('11', 'Non IT Issue'),
('12', 'Unknown Root Cause'),
('13', 'Request Fulfilled'),
('14', 'Request Denied'),
('15', 'Security Issue');

2. 关联查询替换：

SELECT 
    COALESCE(rcm.RootCauseName, t.CFCFIncidentRootCause) AS CFCFIncidentRootCause
FROM YourTableName t
LEFT JOIN RootCauseMapping rcm ON t.CFCFIncidentRootCause = rcm.Code;

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