gpt4 book ai didi

sql - 将值分配给 SQL Server 中的多行

转载 作者:行者123 更新时间:2023-12-01 12:24:33 24 4
gpt4 key购买 nike

我需要有关 SQL Server 的帮助,了解如何将行值分配给具有相同 ID 的多行。为了说明,

  • Id = ProductInventoryCode
  • 数量 = 库存数量

对于分发:

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

这是一个示例屏幕输出

enter image description here

上面的结果是 145 Branches,下面我们用来分配 ForDistributionQty 字段,它是 3130,我最后得到的分数 (DistVal = 21.586) 对于这个问题来说是不正确的,它应该是一个整数,比如21,但是,如果它只是 21,那么 21 x 145 就是 3045,少了 85 个单位。

最佳答案

这里我们对数值进行分配,然后对数量最大(任意)的记录做最后的“调整”。但归根结底,数学是可行的,分布值是正方形的。

注意:不确定为什么在您的样本中 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

关于sql - 将值分配给 SQL Server 中的多行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41364108/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com