sql - SQL Server：案例表达式返回 0 而不是空字符串

Question

以下是不使用转换为 varchar 时返回 0 的查询。它比较整数值

SELECT 
    *,
    CASE 
       WHEN Duration IS NULL
          THEN CONVERT(VARCHAR, '') 
          ELSE Duration 
    END AS Duration  
FROM
    Contribution 
WHERE 
    CreatedBy = @in_userID  AND ContentStatus = @ContentStatus   

UNION ALL

SELECT 
    *,
    CASE 
       WHEN Duration IS NULL
          THEN CONVERT(VARCHAR, '')  
          ELSE Duration 
    END AS Duration  
FROM
    Training  
WHERE 
    CreatedBy = @in_userID  AND ContentStatusValue = @TrainingStatus

在使用 union all 时，不使用 convert 大小写返回 0 而不是 ''。使用转换时问题已解决。

谁能说出为什么会这样？

标签： sqlsql-servertsql

Answer 1

我怀疑你Duration有INT类型，所以你可以进行对话：

SELECT c.col1, c.col2, ISNULL(CAST(c.Duration AS VARCHAR(255)),'') Duration  
FROM Contribution c
WHERE c.CreatedBy = @in_userID  AND
      c.ContentStatus = @ContentStatus 
UNION ALL
SELECT t.col1, t.col2, ISNULL(CAST(t.Duration AS VARCHAR(255), '') 
FROM Training t 
WHERE t.CreatedBy = @in_userID AND
      t.ContentStatusValue = @TrainingStatus;