MySQL中使用cast函数将字段转为decimal但是求和后去除最后的0

• 背景：MySQL中使用case函数将varchar类型字段转换为decimal类型后进行sum计算，但是由于decimal指定了小数点后的位数，所以查询后带有‘0’，而返回给前端表示需要去除小数点后无意义的‘0’
• 

解决方案：不带任何负载的格式化字符串，可以使用TRIM()函数去除末尾的0

SELECT
	brf.type_id AS type_id,
	COUNT( brf.id ) AS amount,
	COALESCE ( SUM( IFNULL( brf.area, 0 ) ), 0 ) AS area ,
	SUM(CAST(IFNULL( brf.area, 0 ) AS DECIMAL(10, 3))) AS area2,
	TRIM(TRAILING '0' FROM  SUM(CAST(IFNULL( brf.area, 0 ) AS DECIMAL(10, 3))) )AS area3,
	SUBSTRING_INDEX(SUM(CAST(IFNULL( brf.area, 0 ) AS DECIMAL(10, 3))),'.',1)+0 AS area4
FROM
	biz_resource_forest brf 
WHERE
	brf.del_flag = '0' 
GROUP BY
	type_id

• 结语：正常解决问题