在 SQL 中将 INT 转换为 FLOAT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9410686/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert INT to FLOAT in SQL
提问by alkhader
I have this Query :
我有这个查询：
SELECT sl.sms_prefix, sum( sl.parts ) , cp.country_name, CAST(SUM(sl.parts) AS NUMERIC(10,4)) * CAST(cp.price AS NUMERIC(10,4))
FROM sms_log sl, sms_transaction st, country_prefix cp
WHERE st.customer_id =1
AND st.sendtime >=1329865200
AND st.sendtime <=1330037999
AND st.sms_trans_id = sl.trans_id
AND sl.sms_prefix = cp.prefix
AND st.customer_id = cp.customer_id
GROUP BY sl.sms_prefix
LIMIT 0 , 30
Result:
结果：
sms_prefix sum( sl.parts ) country_name price total
==================================================================================
45 2 Denmark 0.01 0.019999999552965
63 3 Philippines 2 6
As you see the "total"is not correct for Denmarkbecause sum( sl.parts )=2Multiply with 0.01the total should be 0.02.
如您所见，"total"是不正确的，Denmark因为sum( sl.parts )=2乘以0.01总数应该是0.02。
Price field is FLOAThow I can CAST the total to float ?
价格字段是FLOAT如何将总数转换为浮动的？
Regards,
问候，
回答by MatBailie
While @Heximal's answer works, I don't personally recommend it.
虽然@Heximal 的回答有效，但我个人并不推荐它。
This is because it uses implicitcasting. Although you didn't type CAST, either the SUM()or the 0.0need to be cast to be the same data-types, before the +can happen. In this case the order of precedence is in your favour, and you get a float on both sides, and a float as a result of the +. But SUM(aFloatField) + 0does not yield an INT, because the 0is being implicitlycast to a FLOAT.
这是因为它使用隐式转换。尽管您没有输入CAST，但是在发生之前，需要将SUM()或0.0强制转换为相同的数据类型+。在这种情况下，优先顺序对您有利，两边都有浮动，而+. 但SUM(aFloatField) + 0不会产生 INT，因为0被隐式转换为 FLOAT。
I find that in most programming cases, it is much preferable to be explicit. Don't leave things to chance, confusion, or interpretation.
我发现在大多数编程情况下，显式更可取。不要把事情留给机会、困惑或解释。
If you want to be explicit, I would use the following.
如果你想明确，我会使用以下内容。
CAST(SUM(sl.parts) AS FLOAT) * cp.price
I won't discuss whether NUMERIC or FLOAT (fixed point, instead of floating point)is more appropriate, when it comes to rounding errors, etc. I'll just let you google that if you need to, but FLOAT is so massively misused that there is a lot to read about the subject already out there.
我不会讨论 NUMERIC 或 FLOAT （定点，而不是浮点）是否更合适，在涉及舍入错误等方面。如果需要，我会让你谷歌搜索，但 FLOAT 被大量滥用已经有很多关于这个主题的书了。
You can try the following to see what happens...
您可以尝试以下操作，看看会发生什么...
CAST(SUM(sl.parts) AS NUMERIC(10,4)) * CAST(cp.price AS NUMERIC(10,4))
回答by heximal
In oracle db there is a trick for casting int to float (I suppose, it should also work in mysql):
在 oracle db 中有一个将 int 转换为 float 的技巧（我想，它也应该在 mysql 中工作）：
select myintfield + 0.0 as myfloatfield from mytable
