适用环境:MSSQL 2005+。其中05需修改部分语句的写法才行,如:
--变量的声明和赋值需分开写 DECLARE @scale INT = 0 --需改为如下 DECLARE @scale INT; SET @scale = 0 --05不支持+=这样的复合运算符 SET @scale += 1 --需改为如下 SET @scale = @scale + 1
功能:
将一个数字(整数或有限小数)分配成指定份,每份一行,返回一个单列表格。
使用示例:
可见,这不是除法,而是为了尽可能公平的把数字分配完,该分配法有如下
特点:
- 不存在除不尽的情况
- 能保证所有份数之和与原数相等
- 结果精度与原数(被分配数)精度一致。3.470的精度视为0.01,所以它分成3份是1.16、1.16、1.15,而不是1.157、1.157、1.156
- 结果最多只会出现两种数值,大值与小值之差为原数精度的1个单位。即在原数精度范围内,各份之间的差值保证最小,以此做到尽可能公平的分配。如上例1.16与1.15相差0.01
- 若被分配数为NULL、或份数为NULL或0,返回0行
像这种分配法我估计在多种场景都有在用,它应该有个名堂的,只是我不知道,还请知道的猿友告知,谢谢。
函数源码:
/*-------------------- 函数:分配0.01 Author:AhDung Update:201504211157 --------------------*/ ALTER FUNCTION dbo.AllotToTable(@num DECIMAL(16,4), @div TINYINT) RETURNS @t TABLE(Val DECIMAL(16,4)) BEGIN IF @num IS NULL OR @div IS NULL OR @div=0 RETURN IF @div=1 --除数为1,直接返回1行 BEGIN INSERT @t SELECT @num RETURN END IF @num=0 --被除数为0,直接返回@div行0 BEGIN INSERT @t SELECT 0 FROM FMakeRows(@div) RETURN END /*得到@num的真实精度*/ DECLARE @scale INT=0, @numToInt DECIMAL(16,4)=@num WHILE @numToInt % 1 <> 0 BEGIN SET @numToInt *= 10 SET @scale += 1 END /*填充元素(舍弃过的)*/ DECLARE @roundDownItem DECIMAL(16,4)=ROUND(@num / @div, @scale, 1) INSERT @t SELECT @roundDownItem FROM FMakeRows(@div) /*将余数均分到top n行。余数之所以不采用@num % @roundDownItem,是因为后者为1时就不对*/ DECLARE @mod DECIMAL(16,4) = @num - @roundDownItem*@@ROWCOUNT IF @mod<>0 BEGIN DECLARE @pow INT = POWER(10, @scale) UPDATE TOP (CAST(ABS(@mod) * @pow AS INT)) @t SET Val += SIGN(@mod)/@pow END RETURN END
函数中用到了一个叫做FMakeRows的自定义表值函数,有劳移步至这篇博文取用:http://www.cnblogs.com/ahdung/p/4195509.html,当然你可以小加改造,让它不依赖外部函数。
有尝试过用排名函数NTILE实现,借助它的组分配能力,但性能不理想,因为被分配数有多大,就得构造多大行数的表格,空间和时间都不值得。另请老鸟指点改善,谢谢。
-文毕-