在SQL Server中将值分发到多行

Question

我需要关于如何将一行值分配给具有相同id的几行的SQL Server帮助。为了说明，

Id = ProductInventoryCode Qty = QuantityInStock

对于Distribution：

Id |   Qty  | TotalNoOfBranchesWithId
---+--------+-------------------------
1  |   40   |     2
2  |   33   |     3
3  |   21   |     2

一个将接收分布式值的表格。

Id | BranchCode |    Qty | QtyFromForDistributionTable
-------------------------------------------------------
1       101          13            20
1       102           8            20
2       101          10            11
2       102           2            10
2       103           3            12
3       101           1            11
3       102          12            10

尽可能地，每个 ID 和分支的分布应该近似相等。

我得到了类似下面的东西，但有点困惑，迷失了方向。

with rs as 
(
    select 
        r.*, cume.cumequantity, 
        coalesce(s.shipped, 0) AS shipped
    from 
        tmpForDistribution r
    cross apply
        (SELECT SUM([QuantityInStock]) AS cumequantity
         FROM tmpForDistribution r2
         WHERE r2.ProductInventoryCode = r.ProductInventoryCode) cume 
    left join
        (SELECT ProductInventoryCode, COUNT(ProductInventoryCode) AS shipped
         FROM tmpDistributed s      
         GROUP BY s.ProductInventoryCode) s ON r.ProductInventoryCode = s.ProductInventoryCode      
)
select 
    rs.ProductInventoryCode, rs.cumequantity, rs.QuantityInStock,
    ***"how to distribute"***
from rs

我目前正在使用SQL Server 2008

这是一个样例屏幕输出

上面的结果是145个分支机构，下面我们使用用于分配数量字段的ForDistributionQty，该字段为3130，我最终得到了一个小数（DistVal = 21.586），这对于此问题来说不正确，它应该是一个整数，例如21，但是，如果只有21，则21 x 145仅为3045，少了85个单位。

- mirageservo

Answer 1

在这里，我们分配值，然后对数量最大（任意）的记录进行最终“调整”。但归根结底，数学是正确的，分配的值是平均的。

注意：不确定为什么在您的示例中ID 2没有获得均匀分布。

Declare @Table table (Id int,BranchCode int,Qty int)
Insert Into @Table values
(1,       101,          13),
(1,       102,           8),
(2,       101,          10),
(2,       102,           2),
(2,       103,           3),
(3,       101,           1),
(3,       102,          12)

Declare @Dist table (ID int,Qty int)
Insert Into @Dist values
(1,40),
(2,33),
(3,49)

;with cte0 as (
        Select A.*
              ,ToDist  = cast(D.Qty as int)
              ,DistVal = cast(D.Qty as int)/C.Cnt 
              ,RN      = Row_Number() over (Partition By A.ID Order By cast(D.Qty as int)/C.Cnt Desc,A.Qty Desc)
         From  @Table A
         Join  (Select ID,Cnt=count(*) from @Table Group By ID) C on A.ID=C.ID
         Join  @Dist D on A.ID=D.ID  )
, cte1 as (
        Select ID,AdjVal=Sum(DistVal)-max(ToDist) From cte0 Group By ID
)
Select A.ID
      ,A.BranchCode
      ,A.Qty
      ,DistVal = DistVal - case when A.RN<=abs(AdjVal) then 1*sign(AdjVal) else 0 end
 From cte0 A
 Join cte1 B on (A.ID=B.Id)
 Order By 1,2

返回

ID  BranchCode  Qty DistVal
1   101         13  20
1   102         8   20
2   101         10  11
2   102         2   11
2   103         3   11
3   101         1   24
3   102         12  25

- John Cappelletti

---

尽管总和相等，但我得到了小数，但我只需要整数，不需要均匀分布的数量（与我的原始需求相反）。 有没有办法控制和确保分配的数量相等，同时去掉小数点。 - mirageservo

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - John Cappelletti

由于四舍五入的值被截断，因此缺少了很多数量，例如带有小数的金额为21.56，执行DistVal = cast(D.Qty as INT)/C.Cnt后仅变成了21，因此当您将其乘以分配总数时，会缺失很多。 - mirageservo

@paulpolo 显然我在你的需求中漏掉了一些东西。也许提供一些更具代表性的样本数据会有所帮助。提供的方法将分配整数而不是分数。 - John Cappelletti

这是一个示例，3130个数量要分配，145个接收者，因此3130/145=21.586，如果我们将其转换为整数，则变为21，因此我会丢失0.586 * 145，有没有办法可以通过黑客方式，获取分配的总和作为整数，然后获取缺失的部分，再次进行分配？ - mirageservo

Answer 2

如果您可以接受小数值，子查询似乎会给出更好的查询计划（在SQL 2014上进行了测试，并且有一些合理的键位，这避免了表锁和一些额外的索引扫描）：

Declare @Table table (Id int,BranchCode int,Qty int, primary key(id, branchcode))
Insert Into @Table values
(1,       101,          13),
(1,       102,           8),
(2,       101,          10),
(2,       102,           2),
(2,       103,           3),
(3,       101,           1),
(3,       102,          12)

Declare @Dist table (ID int primary key,Qty int)
Insert Into @Dist values
(1,40),
(2,33),
(3,21)


SELECT
    t.id
   ,t.BranchCode
   ,t.Qty
   ,(d.Qty / CAST((SELECT COUNT(*) as cnt FROM @table t2 where t.id = t2.id) AS decimal(10,2))) as DistributedQty   
FROM @Table t
INNER JOIN @Dist d
ON d.id = t.Id

输出：

Id  BranchCode  Qty DistributedQty
1   101         13  20.00000000000
1   102         82  20.00000000000
2   101         10  11.00000000000
2   102         21  11.00000000000
2   103         31  11.00000000000
3   101         11  10.50000000000
3   102         12  10.50000000000

如果您需要DistributedQty是一个整数并保留余数，那么我想不出比@John Cappelletti的更好的解决方案，需要注意的是，不均匀的数量可能不会像您希望的那样精确地均匀分布（例如，由三个分配的32将导致12/10/10的分布，而不是11/11/10的分布）。

- Dan Field

---

就我个人而言，我更喜欢你所呈现的精确性（头韵很有趣）+1。 - John Cappelletti