我尝试使用TRIM(VARCHAR_FORMAT( value ), '9,999,990.99')进行数值格式化，但执行失败。请问是否有简便方法将查询结果转换为更易读的格式？我需要让结果保留两位小数，并正确使用千位分隔符（如0,000格式）。希望优化SQL中标记为黄色的数值字段的显示格式。以下是我的SQL语句：

WITH X AS (
 SELECT 1 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME,
        T.BILL_NUMBER CURRENT_FB, T.WEIGHT CURRENT_WEIGHT,
        TRIM(VARCHAR_FORMAT((T.CHARGES + T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END), '9,999,990.99') as CURRENT_REVENUE,
        NULL PREVIOUS_FB, NULL PREVIOUS_WEIGHT, NULL PREVIOUS_REVENUE
 FROM TLORDER T
 INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
 INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
 WHERE 1 = 1
 AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
 AND T.EXTRA_STOPS <> 'Child'
 AND T.BILL_NUMBER NOT IN ('0', 'NA')
 AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 90 days AND CURRENT TIMESTAMP -60 days
 UNION ALL
 SELECT 2 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME,
        NULL CURRENT_FB, NULL CURRENT_WEIGHT, NULL CURRENT_REVENUE,
        T.BILL_NUMBER PREVIOUS_FB, T.WEIGHT PREVIOUS_WEIGHT,
        TRIM(VARCHAR_FORMAT((T.CHARGES + T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END), '9,999,990.99') AS PREVIOUS_REVENUE
 FROM TLORDER T
 INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
 INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
 WHERE 1 = 1
 AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
 AND T.EXTRA_STOPS <> 'Child'
 AND T.BILL_NUMBER NOT IN ('0', 'NA')
 AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 60 days AND CURRENT TIMESTAMP
)
SELECT 1 SORT, COMPANY, CLIENT_ID, CLIENT_NAME,
       COUNT(DISTINCT CURRENT_FB) CURRENT_FB, COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB,
       ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
       SUM(CURRENT_WEIGHT) CURRENT_WEIGHT, SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
       ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
       TRIM(VARCHAR_FORMAT(SUM(CURRENT_REVENUE),'9,999,990.99')) as CURRENT_REVENUE,
       TRIM(VARCHAR_FORMAT(SUM(PREVIOUS_REVENUE),'9,999,990.99')) as PREVIOUS_REVENUE,
       ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2) REVENUE_DIFF
FROM X
GROUP BY COMPANY, CLIENT_ID, CLIENT_NAME
UNION ALL
SELECT 2 SORT, COMPANY, 'TOTAL' CLIENT_ID, NULL CLIENT_NAME,
       COUNT(DISTINCT CURRENT_FB) CURRENT_FB, COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB,
       ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
       SUM(CURRENT_WEIGHT) CURRENT_WEIGHT, SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
       ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
       TRIM(VARCHAR_FORMAT(SUM(CURRENT_REVENUE),'9,999,990.99')) AS CURRENT_REVENUE,
       TRIM(VARCHAR_FORMAT(SUM(PREVIOUS_REVENUE),'9,999,990.99')) AS PREVIOUS_REVENUE,
       ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2) REVENUE_DIFF
FROM X
GROUP BY COMPANY

一眼就能看出问题所在：你把VARCHAR_FORMAT的参数顺序搞反了！这个函数的正确语法是VARCHAR_FORMAT(要格式化的数值, 格式模板)，而你错误地把格式模板放到了TRIM的第二个参数位置（TRIM的第二个参数是要移除的字符，不是格式模板），这直接导致了执行失败。

1. 核心修正点 #

正确的格式化写法应该是：

TRIM(VARCHAR_FORMAT(你的数值表达式, '9,999,990.99'))

比如你CTE里的CURRENT_REVENUE字段，应该改成：

TRIM(VARCHAR_FORMAT((T.CHARGES + T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END, '9,999,990.99')) as CURRENT_REVENUE

2. 完整修正后的SQL #

我已经把所有错误的VARCHAR_FORMAT调用都修正好了，确保所有目标数值字段都能正确生成带千位分隔符、保留两位小数的格式：

WITH X AS (
 SELECT 1 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME,
        T.BILL_NUMBER CURRENT_FB, T.WEIGHT CURRENT_WEIGHT,
        -- 修正VARCHAR_FORMAT参数顺序
        TRIM(VARCHAR_FORMAT((T.CHARGES + T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END, '9,999,990.99')) as CURRENT_REVENUE,
        NULL PREVIOUS_FB, NULL PREVIOUS_WEIGHT, NULL PREVIOUS_REVENUE
 FROM TLORDER T
 INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
 INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
 WHERE 1 = 1
 AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
 AND T.EXTRA_STOPS <> 'Child'
 AND T.BILL_NUMBER NOT IN ('0', 'NA')
 AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 90 days AND CURRENT TIMESTAMP -60 days
 UNION ALL
 SELECT 2 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME,
        NULL CURRENT_FB, NULL CURRENT_WEIGHT, NULL CURRENT_REVENUE,
        T.BILL_NUMBER PREVIOUS_FB, T.WEIGHT PREVIOUS_WEIGHT,
        -- 修正VARCHAR_FORMAT参数顺序
        TRIM(VARCHAR_FORMAT((T.CHARGES + T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END, '9,999,990.99')) AS PREVIOUS_REVENUE
 FROM TLORDER T
 INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
 INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
 WHERE 1 = 1
 AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
 AND T.EXTRA_STOPS <> 'Child'
 AND T.BILL_NUMBER NOT IN ('0', 'NA')
 AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 60 days AND CURRENT TIMESTAMP
)
SELECT 1 SORT, COMPANY, CLIENT_ID, CLIENT_NAME,
       COUNT(DISTINCT CURRENT_FB) CURRENT_FB, COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB,
       ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
       SUM(CURRENT_WEIGHT) CURRENT_WEIGHT, SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
       ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
       -- 修正VARCHAR_FORMAT参数顺序
       TRIM(VARCHAR_FORMAT(SUM(CURRENT_REVENUE),'9,999,990.99')) as CURRENT_REVENUE,
       -- 修正VARCHAR_FORMAT参数顺序
       TRIM(VARCHAR_FORMAT(SUM(PREVIOUS_REVENUE),'9,999,990.99')) as PREVIOUS_REVENUE,
       ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2) REVENUE_DIFF
FROM X
GROUP BY COMPANY, CLIENT_ID, CLIENT_NAME
UNION ALL
SELECT 2 SORT, COMPANY, 'TOTAL' CLIENT_ID, NULL CLIENT_NAME,
       COUNT(DISTINCT CURRENT_FB) CURRENT_FB, COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB,
       ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
       SUM(CURRENT_WEIGHT) CURRENT_WEIGHT, SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
       ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
       -- 修正VARCHAR_FORMAT参数顺序
       TRIM(VARCHAR_FORMAT(SUM(CURRENT_REVENUE),'9,999,990.99')) AS CURRENT_REVENUE,
       -- 修正VARCHAR_FORMAT参数顺序
       TRIM(VARCHAR_FORMAT(SUM(PREVIOUS_REVENUE),'9,999,990.99')) AS PREVIOUS_REVENUE,
       ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2) REVENUE_DIFF
FROM X
GROUP BY COMPANY

3. 额外优化建议 #

如果你的DB2版本支持，推荐使用DECIMAL_FORMAT函数替代，它专门针对数值格式化，模板更通用：

TRIM(DECIMAL_FORMAT(你的数值表达式, '#,##0.00'))

这个模板#,##0.00和9,999,990.99效果一致，但优势在于可以自动适配任意大小的数值，不会因为数值超出模板范围而显示###########这样的溢出标记。

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