sqlserver sum（）后再除以10000后丢精度

一、修改前

select year,month,sum(ysl)ysl1,sum(isnull(ysl_sy_dx,0))ysl2,sum(isnull(actual_consume_total,0)) as ysl1_1,(sum(isnull(actual_consume_groundwater,0))) as ysl2_1
from(select re.xian_cd,year,month,sum(isnull(ysl,0)) ysl,sum(isnull(ysl_sy_dx,0))ysl_sy_dx,	
			cast((cast(sum(isnull(ysl,0)) as decimal(18,6)) / 10000) as decimal(18,6)) as actual_consume_total,
			cast((cast(sum(isnull(ysl_sy_dx,0)) as decimal(18,6)) / 10000) as decimal(18,6)) as actual_consume_groundwater
		from test
		group by xian_cd,year,month
)a
where year = 2023 and month = 2
group by year,month
order by year,month;

运行结果：

year	month	ysl1（立方米）	ysl2（立方米）	ysl1_1（万立方米）	ysl2_1（万立方米）
2023	2	7464174.287	2423411.559	746.417352	242.341079

二、修改后

select year,month,sum(ysl)ysl1,sum(isnull(ysl_sy_dx,0))ysl2,sum(isnull(actual_consume_total,0)) as ysl1_1,(sum(isnull(actual_consume_groundwater,0))) as ysl2_1
from(select re.xian_cd,year,month,sum(isnull(ysl,0)) ysl,sum(isnull(ysl_sy_dx,0))ysl_sy_dx,	
			cast((cast(sum(isnull(ysl,0)) as decimal(18,6)) / 10000.0) as decimal(18,6)) as actual_consume_total,
			cast((cast(sum(isnull(ysl_sy_dx,0)) as decimal(18,6)) / 10000.0) as decimal(18,6)) as actual_consume_groundwater
		from test
		group by xian_cd,year,month
)a
where year = 2023 and month = 2
group by year,month
order by year,month;

运行结果：

year	month	ysl1（立方米）	ysl2（立方米）	ysl1_1（万立方米）	ysl2_1（万立方米）
2023	2	7864174.287	4423411.559	786.41742870	442.34115590

三、总结

        从上面可以看出，ysl1和ysl1_1、ysl2和ysl2_1的整数部分是一样的，有区别的是小数位（转成万m³后要保留4位小数），第一种方法的差别比较大，第二种换算后就一样了

        区别有两点：

                ①cast(sum(isnull(ysl_sy_dx,0)) as decimal(18,6))，sum()之后县转换成decimal

                ②/10000改成/10000.0