「為什麼 CAST(32.678 AS DECIMAL(5,1)) 返回 32.7 而不是 32.6?」這個看似簡單的四捨五入問題,藏著 ROUND、CAST、CONVERT 三個函數的精度語義差異、隱式截斷行為、數據類型優先級等核心知識點。本文徹底釐清三函數行為邊界,給出精度控制最佳實踐。
SELECT
ROUND(32.678, 1) AS RoundResult, -- 32.700000
CAST(32.678 AS DECIMAL(5,1)) AS CastResult, -- 32.7
CONVERT(NUMERIC(5,1), 32.678) AS ConvertResult; -- 32.7
**結論:三者在「四捨五入保留 1 位小數」這個語義上結果一致,但返回類型、精度保留、截斷行為存在關鍵差異。**
length > 0:保留小數點右側位數length < 0:小數點左側四捨五入 (百位、千位...)function = 0 (默認):四捨五入function <> 0:截斷-- 經典示例 SELECT ROUND(748.58, -1) -- 750.00 (十位四捨五入) SELECT ROUND(748.58, -2) -- 700.00 (百位) SELECT ROUND(748.58, -3) -- 1000.00 (千位) SELECT ROUND(748.58, -4) -- 0.00 (超出位數歸零) -- 截斷模式 SELECT ROUND(150.75, 0) -- 151.00 (四捨五入) SELECT ROUND(150.75, 0, 1) -- 150.00 (截斷) -- 邊界行為 SELECT ROUND(123.9994, 3) -- 123.9990 SELECT ROUND(123.9995, 3) -- 124.0000 (銀行家捨入?實為標準四捨五入)
CAST(expression AS target_type) — ANSI 標準CONVERT(target_type, expression [, style]) — T-SQL 擴展,支援日期格式 styleDECIMAL(p,s) / NUMERIC(p,s)-- 精度截斷演示
SELECT CAST('123.456' AS DECIMAL) -- 123 (默認 DECIMAL(18,0),整數)
SELECT CAST('123.456' AS DECIMAL(38,2)) -- 123.46 (四捨五入保留 2 位)
-- CONVERT 風格參數 (僅日期/時間/浮點數格式有效)
SELECT CONVERT(VARCHAR, GETDATE(), 120); -- 2023-10-27 10:15:21
SELECT
ROUND(123.75633, 2, 0) AS RoundMode, -- 123.76000 (四捨五入)
ROUND(123.75633, 2, 1) AS TruncMode; -- 123.75000 (截斷!先截後捨)
關鍵區別:function=1 時,先按 length 截斷,再四捨五入(實際是直接截斷)。這與直覺的「先四捨五入再截斷」相反,極易導致業務邏輯錯誤。
| 業務場景 | 推薦方案 | 避免方案 | 理由 |
|---|---|---|---|
| 純數值四捨五入保留 N 位 | ROUND(val, N) | CAST/CONVERT | 語義明確、不改變類型、可控制負 length |
| 存儲/傳輸需固定精度類型 | CAST(val AS DECIMAL(p,s)) | ROUND + 隱式轉換 | 明確定義存儲精度、避免下游隱式轉換 |
| 顯示格式化(前端展示) | 應用層格式化 / FORMAT() | SQL 層 ROUND/CAST | 分離關注點、支援本地化、千分位、貨幣符號 |
| 財務計算(銀行家捨入) | 自定義函數 / CLR / 應用層 | ROUND (標準四捨五入) | SQL Server 無內建銀行家捨入 |
| 截斷而非四捨五入 | ROUND(val, N, 1) 或 FLOOR(val * POWER(10,N))/POWER(10,N) | CAST (會四捨五入) | CAST 無截斷模式 |
CAST(ROUND(val,2) AS DECIMAL(10,2)) 雙重四捨五入風險 → ✅ 直接 CAST(val AS DECIMAL(10,2))DECIMAL(18,4)CREATE FUNCTION dbo.RoundBankers(@Value DECIMAL(38,10), @Decimals INT)
RETURNS DECIMAL(38,10)
AS BEGIN
DECLARE @Factor DECIMAL(38,10) = POWER(10, @Decimals);
DECLARE @Scaled DECIMAL(38,10) = @Value * @Factor;
DECLARE @IntPart BIGINT = FLOOR(@Scaled);
DECLARE @FracPart DECIMAL(38,10) = @Scaled - @IntPart;
IF @FracPart > 0.5 OR (@FracPart = 0.5 AND @IntPart % 2 = 1)
SET @IntPart = @IntPart + 1;
RETURN @IntPart / @Factor;
END;
金句:數據庫層負責「精度正確存儲與計算」,應用層負責「精度正確展示」。不要指望 SQL Server 的 ROUND/CAST 解決所有格式化問題——那是前端/報表工具的職責。