sql - TSQL - 根据一个字段将行分组

Question

这是我之前问题的修改版本:TSQL equally divide resultset to groups and update them

我的数据库有 2 个表，如下所示:

Orders 表有如下数据:

OrderID    OperatorID    GroupID        OrderDesc    Status    Cash    ...
--------------------------------------------------------------------------
      1             1          1      small_order         1     300 
      2             1          1    another_order         1       0 
      3             1          2      xxxxxxxxxxx         2    1000 
      5             2          2      yyyyyyyyyyy         2     150 
      9             5          1      xxxxxxxxxxx         1      50 
     10          NULL          2      xxxxxxxxxxx         1     150 
     11          NULL          3      xxxxxxxxxxx         1     -50 
     12             4          1      xxxxxxxxxxx         1     200 

运算符表:

OperatorID    Name    GroupID    Active
---------------------------------------
      1       John          1         1
      2       Kate          1         1
      4       Jack          2         1
      5       Will          1         0
      6        Sam          3         0

我可以使用以下查询将我的记录集平均划分为相同的组:

SELECT o.*, op.operatorName AS NewOperator, op.operatorID AS NewOperatorId
FROM (SELECT o.*, (ROW_NUMBER() over (ORDER BY newid()) % numoperators) + 1 AS randseqnum
      FROM Orders o CROSS JOIN
     (SELECT COUNT(*) AS numoperators FROM operators WHERE operators.active=1) op
      WHERE o.status in (1,3)
     ) o JOIN
     (SELECT op.*, ROW_NUMBER() over (ORDER BY newid()) AS seqnum
      FROM Operators op WHERE op.active=1
     ) op
     ON o.randseqnum = op.seqnum ORDER BY o.orderID

演示可用:http://sqlfiddle.com/#!3/ff47b/1

使用上面的脚本，我可以将订单分成(几乎)相等的组，但基于运算符(operator)的数量或订单，但我需要修改它，以便它根据总和或 Cash 将运算符(operator)分配给订单。订单。

例如:

如果我有 6 个订单 Cash值:300、0、50、150、-50、200 它们相加得到 650。
我的脚本应该分配给 3 个运算符(operator)随机 2 个订单，随机总和为 Cash用于订单。
我想要得到的是将例如 300,-50 分配给 operator1, 200, 0 分配给第二个，将 150, 50 分配给第三个。
希望这听起来很清楚:)

这是我希望得到的示例输出:

ORDERID  OPERATORID  GROUPID    DESCRIPTION  STATUS  CASH  NEWOPERATORID
------------------------------------------------------------------------
      1           1        1    small_order       1   300              2
      2           1        1  another_order       1     0              1
      9           5        1    xxxxxxxxxxx       1    50              4
     10      (null)        2    xxxxxxxxxxx       1   150              4
     11      (null)        3    xxxxxxxxxxx       1   -50              2
     12           4        1    xxxxxxxxxxx       1   200              1

我如何(如果可以的话)将运算符(operator)分配给我的订单，以便总和或 Cash将最接近平均值

Answer 1

如果我没理解错的话，您能否通过按最大、然后是最小、次大、次小等顺序对 Cash 列进行排序来获得您想要的结果。像这样:

ROW_NUMBER() over (order by CASE WHEN CashOrder % 2 = 1 then Cash else -Cash END) as splitCash

您在查询中提供 CashOrder 的位置

ROW_NUMBER() over (ORDER by CASH) as CashOrder

然后根据这个拆分值指定每个运算符，即(对于三个运算符):

splitCash%3 +1