我有以下SQL查询
SELECT MAX(averageSales)
FROM (
SELECT AVG(TotalSale) AS averageSales
FROM [Practice].[dbo].[Sales]
GROUP BY CAST(SalesPerson AS NVARCHAR(100))
) AS Query1;
我得到的结果是250
,但我希望将结果显示为250.0000
。我已经研究过SQL的RIGHT
和LEFT
函数,但还不能搞清楚。averageSales的数据类型为int。
我也尝试了以下方法
SELECT
MAX(averageSales),
CAST(MAX(averageSales) AS decimal(4,4))
FROM (
SELECT AVG(TotalSale) AS averageSales
FROM [Practice].[dbo].[Sales]
GROUP BY CAST(SalesPerson AS NVARCHAR(100))
) AS Query1;
但这会导致
将int转换为numeric数据类型时出现
算术溢出错误。
发布于 2018-07-27 07:46:21
使用FORMAT(..., '#.0000')
SELECT FORMAT(MAX(averageSales), '#.0000') FROM
(
SELECT AVG(TotalSale) AS averageSales FROM [Practice].[dbo].[Sales]
GROUP BY CAST(SalesPerson AS NVARCHAR(100))
) AS Query1;
发布于 2018-07-27 07:53:28
将CAST
与DECIMAL
一起使用而不是NVARCHAR
GROUP BY CAST(SalesPerson as DECIMAL(8,4))
https://stackoverflow.com/questions/51548877
复制相似问题