以小数点后2位显示SQL计算结果

Question

我编写了一个简单的查询来查找两列的百分比，但是我希望在只有两位小数的列中得到结果。这是我写的，但我得到错误，我似乎无法修复它。

SELECT 
Small_Bags,
Total_Bags,
(Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent
(Select CAST (Small_Bags_Percent AS DECIMAL (10,2)))
FROM Table_Bags
WHERE Total_Bags <>0

Answer 1

不能在同一查询中使用列别名。你可以这样做。

select Small_Bags
, Total_Bags
, (Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent
, cast(((Small_Bags / Total_Bags) * 100) as decimal(10,2))
from Table_Bags
where Total_Bags > 0

或使用子查询

select t1.Small_Bags, t1.Total_Bags, t1.Small_Bags_Percent
, cast(t1.Small_Bags_Percent as decimal(10,2))
from 
(select Small_Bags
, Total_Bags
, (Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent
, cast(((Small_Bags / Total_Bags) * 100) as decimal(10,2))
from Table_Bags
where Total_Bags > 0) t1

Answer 2

两个函数:

SELECT ROUND(12345.678, 2), FORMAT(12345.678, 2);
+---------------------+----------------------+
| ROUND(12345.678, 2) | FORMAT(12345.678, 2) |
+---------------------+----------------------+
|            12345.68 | 12,345.68            |
+---------------------+----------------------+

注意其中一个还添加了&;thousand -separators&;

(同样，不需要子查询)

Answer 3

如果这是MySQL，我可以从查询中看到大约3个可能的错误原因。

1. Small_Bags_Percent后面没有逗号
2. CAST ( ...不应该在左括号之间有空格，所以它应该是CAST( ...。
3. 选择Small_Bags_Percent的方式错误。这样做是可能的:

SELECT 
Small_Bags,
Total_Bags,
(Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent,
CAST(((SELECT Small_Bags_Percent)) AS DECIMAL (10,2))
FROM Table_Bags
WHERE Total_Bags <>0

但是，如果你没有理由有两个Small_Bags_Percent列，为什么不直接CAST()到第一个呢?

SELECT 
Small_Bags,
Total_Bags,
CAST((Small_Bags / Total_Bags) * 100 AS DECIMAL (10,2)) AS Small_Bags_Percent
FROM Table_Bags
WHERE Total_Bags <>0