如何使计算出的 SQL Server 百分比值仅显示两位小数

Question

我有这个TSQL：

CREATE TABLE #TEMPCOMBINED(
  DESCRIPTION VARCHAR(200),
  WEEK1USAGE DECIMAL(18,2),
  WEEK2USAGE DECIMAL(18,2),
  USAGEVARIANCE AS WEEK2USAGE - WEEK1USAGE,
  WEEK1PRICE DECIMAL(18,2),
  WEEK2PRICE DECIMAL(18,2),
  PRICEVARIANCE AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE AS (WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0)
);
INSERT INTO #TEMPCOMBINED (DESCRIPTION, 
WEEK1USAGE, WEEK2USAGE, WEEK1PRICE, WEEK2PRICE)
SELECT T1.DESCRIPTION, T1.WEEK1USAGE, T2.WEEK2USAGE, 
T1.WEEK1PRICE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE

。它按照我想要的方式工作（显示"使用情况"和"价格"列的小数点后两位的值），除了计算的百分比值（PRICEVARIANCEPERCENT）显示的值比 Cyrano de Bergerac 的鼻子长，例如"0.0252707581227436823"

我怎样才能强制它代替"0.03"（等等）？

Answer 1

尝试在创建表时显式强制转换计算列，如下所示：

CREATE TABLE #TEMPCOMBINED(
  DESCRIPTION VARCHAR(200),
  WEEK1USAGE DECIMAL(18,2),
  WEEK2USAGE DECIMAL(18,2),
  USAGEVARIANCE AS WEEK2USAGE - WEEK1USAGE,
  WEEK1PRICE DECIMAL(18,2),
  WEEK2PRICE DECIMAL(18,2),
  PRICEVARIANCE AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE AS CAST((WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0) AS DECIMAL(18,2))
);

Answer 2

使用 ROUND 函数

SELECT ROUND(column_name,decimals) FROM table_name;

Answer 3

使用 ROUND（） 函数。看看这里和这里