【SQL】分配函数一枚[AllotToTable]

适用环境：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.01Author:AhDungUpdate:201504211157--------------------*/ALTER FUNCTION dbo.AllotToTable(@num DECIMAL(16,4), @div TINYINT)RETURNS @t TABLE(Val DECIMAL(16,4))BEGINIF @num IS NULL OR @div IS NULL OR @div=0 RETURNIF @div=1 --除数为1，直接返回1行BEGIN INSERT @t SELECT @num RETURNENDIF @num=0 --被除数为0，直接返回@div行0BEGIN INSERT @t SELECT 0 FROM FMakeRows(@div) RETURNEND/*得到@num的真实精度*/DECLARE @scale INT=0, @numToInt DECIMAL(16,4)=@numWHILE @numToInt % 1 <> 0BEGIN SET @numToInt *= 10 SET @scale += 1END/*填充元素（舍弃过的）*/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*@@ROWCOUNTIF @mod<>0BEGIN DECLARE @pow INT = POWER(10, @scale) UPDATE TOP (CAST(ABS(@mod) * @pow AS INT)) @t SET Val += SIGN(@mod)/@powENDRETURNEND

函数中用到了一个叫做FMakeRows的自定义表值函数，有劳移步至这篇博文取用：http://www.cnblogs.com/ahdung/p/4195509.html，当然你可以小加改造，让它不依赖外部函数。

有尝试过用排名函数NTILE实现，借助它的组分配能力，但性能不理想，因为被分配数有多大，就得构造多大行数的表格，空间和时间都不值得。另请老鸟指点改善，谢谢。

-文毕-

原标题：【SQL】分配函数一枚[AllotToTable]

关键词：sql