Teradata中CASE结合CAST格式化价格不符合预期问题咨询

你的问题出在CASE表达式的类型统一规则上，这是SQL里很容易踩的一个坑！

为什么会出现这个结果？ #

CASE表达式要求所有分支返回的数据类型必须一致——如果分支返回的类型不同，数据库会自动进行隐式类型转换，选择精度更高、范围更大的那个类型作为整个CASE的结果类型。

在你的代码里：

• WHEN分支返回的是DECIMAL(38,4)（4位小数）
• ELSE分支返回的是DECIMAL(38,2)（2位小数）

数据库会自动把ELSE分支的DECIMAL(38,2)转换成DECIMAL(38,4)，也就是给10.99补两个零变成10.9900，所以最终PRICE_FORMATTED列的结果就和C列类型一致了，而不是你期望的D列格式。

怎么解决？ #

要实现动态的小数位数显示，不能用数值类型返回，得把结果转换成字符串类型，这样就能分别控制两个分支的格式：

通用SQL写法（兼容大多数数据库）

SELECT 
    CAST(10.99 AS DECIMAL(38,4)) C , 
    CAST(10.99 AS DECIMAL(38,2)) D , 
    CAST(1 AS DECIMAL(38,4)) AS LESS , 
    CAST(1 AS DECIMAL(38,2)) AS GREATER , 
    (CASE 
        WHEN 10.99 < 1  -- 这里可以简化，不用重复CAST，直接用原始数值判断
        THEN CAST(CAST(10.99 AS DECIMAL(38,4)) AS VARCHAR(20)) 
        ELSE CAST(CAST(10.99 AS DECIMAL(38,2)) AS VARCHAR(20)) 
    END) AS PRICE_FORMATTED 
FROM MY_PRICE_TBL ;

针对特定数据库的优化写法

• SQL Server：可以用FORMAT函数直接指定格式字符串

  SELECT 
      CAST(10.99 AS DECIMAL(38,4)) C , 
      CAST(10.99 AS DECIMAL(38,2)) D , 
      CAST(1 AS DECIMAL(38,4)) AS LESS , 
      CAST(1 AS DECIMAL(38,2)) AS GREATER , 
      FORMAT(10.99, CASE WHEN 10.99 < 1 THEN 'N4' ELSE 'N2' END) AS PRICE_FORMATTED 
  FROM MY_PRICE_TBL ;
  

• MySQL：可以用TRUNCATE配合CAST，或者用FORMAT函数

  SELECT 
      CAST(10.99 AS DECIMAL(38,4)) C , 
      CAST(10.99 AS DECIMAL(38,2)) D , 
      CAST(1 AS DECIMAL(38,4)) AS LESS , 
      CAST(1 AS DECIMAL(38,2)) AS GREATER , 
      (CASE 
          WHEN 10.99 < 1 
          THEN CAST(TRUNCATE(10.99, 4) AS CHAR) 
          ELSE CAST(TRUNCATE(10.99, 2) AS CHAR) 
      END) AS PRICE_FORMATTED 
  FROM MY_PRICE_TBL ;
  

• PostgreSQL：用TO_CHAR函数指定格式

  SELECT 
      CAST(10.99 AS DECIMAL(38,4)) C , 
      CAST(10.99 AS DECIMAL(38,2)) D , 
      CAST(1 AS DECIMAL(38,4)) AS LESS , 
      CAST(1 AS DECIMAL(38,2)) AS GREATER , 
      TO_CHAR(10.99, CASE WHEN 10.99 < 1 THEN '99999999999999999999.9999' ELSE '99999999999999999999.99' END) AS PRICE_FORMATTED 
  FROM MY_PRICE_TBL ;
  

注意事项 #

转换成字符串后，PRICE_FORMATTED列就不再是数值类型了，如果后续需要对这个列做数值计算，记得先转回到DECIMAL类型。

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