将varchar值'Rejected'转换为int类型失败的SQL问题求助

错误根源 #

报错信息：

Conversion failed when converting the varchar value 'Rejected' to data type int

问题出在UNION ALL的列结构不匹配：
原代码中，第一个SELECT是经过GROUP BY聚合后的结果，列顺序为ShiftName(INT)、Exception(VARCHAR)、Accepted/Rejected(VARCHAR)、Amount(INT)；但后续所有UNION ALL分支的SELECT语句，直接从临时表取原始行，列顺序是Timestamp(DATETIME)、ShiftName(INT)、Exception(VARCHAR)、Accepted/Rejected(VARCHAR)。

当SQL执行UNION ALL时，会按列的位置强制匹配数据类型：主查询的第四列是INT类型（COUNT(*)的结果），但后续分支的第四列是字符串（如'Rejected'），系统尝试将字符串转换为INT类型，最终触发转换失败错误。

修复方案 #

正确的写法应该是先把所有符合条件的记录通过UNION ALL合并（保证列顺序、类型完全一致），再统一执行聚合统计。

修正后的代码 #

CREATE TABLE #excepts (Id INT, [Timestamp] DATETIME, [ShiftName] INT, [MessageBody] NVARCHAR(500))

INSERT INTO #excepts
EXEC (@sqlCommand);

-- 先合并所有待统计的记录，再统一聚合
SELECT 
    ShiftName, 
    Exception, 
    [Accepted/Rejected], 
    COUNT(*) AS Amount
FROM (
    -- Assign 已接受
    SELECT 
        els.ShiftName, 
        'Assign' AS Exception, 
        'Accepted' AS [Accepted/Rejected]
    FROM #excepts els
    WHERE els.MessageBody LIKE '%entered Assign.'
      AND els.Id + 6 IN (SELECT Id FROM #excepts el WHERE el.MessageBody LIKE '%Accepted%')
    
    UNION ALL

    -- Assign 已拒绝
    SELECT 
        els.ShiftName, 
        'Assign' AS Exception, 
        'Rejected' AS [Accepted/Rejected]
    FROM #excepts  els
    WHERE els.MessageBody LIKE '%entered Assign%'
      AND els.Id + 6 IN (SELECT Id FROM #excepts el WHERE el.MessageBody LIKE '%Ignored%')
    
    UNION ALL

    -- FULL 已接受
    SELECT 
        els.ShiftName, 
        'FULL' AS Exception, 
        'accepted' AS [Accepted/Rejected]
    FROM #excepts  els
    WHERE els.MessageBody LIKE '%entered FULL%'
      AND els.Id + 4 IN (SELECT Id FROM #excepts el WHERE el.MessageBody LIKE '%Accepted%')
    
    UNION ALL

    -- FULL 已拒绝
    SELECT 
        els.ShiftName, 
        'FULL' AS Exception, 
        'rejected' AS [Accepted/Rejected]
    FROM #excepts  els
    WHERE els.MessageBody LIKE '%entered FULL%'
      AND els.Id + 4 IN (SELECT Id FROM #excepts el WHERE el.MessageBody LIKE '%Ignored%')
    
    UNION ALL
        
    -- Arrive 已接受
    SELECT 
        els.ShiftName, 
        'Arrive' AS Exception, 
        'accepted' AS [Accepted/Rejected]
    FROM #excepts  els
    WHERE els.MessageBody LIKE '%entered Arrive.%'
      AND els.Id + 6 IN (SELECT Id FROM #excepts el WHERE el.MessageBody LIKE '%Accepted%')
) AS all_records
GROUP BY 
    ShiftName,
    Exception, 
    [Accepted/Rejected]

关键修复点 #

1. 所有UNION ALL分支的列顺序、类型完全统一，仅保留统计所需的ShiftName、Exception、Accepted/Rejected三列
2. 对合并后的所有记录统一执行GROUP BY和COUNT(*)聚合，避免了聚合结果与原始行混合导致的类型冲突
3. 可选优化：如果需要忽略大小写统计（如'Accepted'和'accepted'视为同一状态），可以用UPPER([Accepted/Rejected])或LOWER([Accepted/Rejected])统一格式

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