我有这个查询:
- 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
结果:
- sms_prefix sum( sl.parts ) country_name price total
- ==================================================================================
- 45 2 Denmark 0.01 0.019999999552965
- 63 3 Philippines 2 6
正如你所看到的,丹麦的“总”不正确,因为sum(sl.parts)= 2乘以0.01,总数应该是0.02.
价格领域是FLOAT我怎么可以CAST总共浮动?
问候,