我的数据库有 3 个表,如下所示:
Orders 表的数据如下:
OrderID OperatorID GroupID OrderDesc Status Cash ...
--------------------------------------------------------------------------
1 1 1 small order 1 100
2 1 1 another order 2 0
3 1 2 xxxxxxxxxxx 2 1000
5 2 2 yyyyyyyyyyy 2 150
9 5 1 xxxxxxxxxxx 1 0
10 NULL 2 xxxxxxxxxxx 1 10
11 NULL 3 xxxxxxxxxxx 1 120
运营商表:
OperatorID Name GroupID Active
---------------------------------------
1 John 1 1
2 Kate 1 1
4 Jack 2 1
5 Will 1 0
6 Sam 3 1
组表:
GroupID Name
---------------
1 G1
2 G2
3 X1
正如您所看到的,John 有 3 个订单,Kate 1 个,Will 1 个,Jack 和 Sam 没有。
现在我想根据某些条件将操作员分配给订单:
- 订单必须有现金>0
- 订单状态必须为 1
- 订单必须属于组 1 或组 2
- 操作员必须处于活动状态(活动=1)
- 操作员必须属于组 1 或组 2
这是我想要得到的结果:
OrderID OperatorID GroupID OrderDesc Status Cash ...
--------------------------------------------------------------------------
1 1 1 small order 1 100 < change
2 1 1 another order 2 0
3 2 2 xxxxxxxxxxx 2 1000 < change
5 4 2 yyyyyyyyyyy 2 150 < change
9 5 1 xxxxxxxxxxx 1 0
10 4 2 xxxxxxxxxxx 1 10 < change
11 NULL 3 xxxxxxxxxxx 1 120
我想洗牌订单并更新操作员ID,以便每次调用此脚本时我都会获得随机分配者操作员ID,但每个操作员都会有相同的数量或订单(接近相等,因为如果我有7个订单,一个人将有3个订单,其余的2)。
我可以用NTILE
将订单分配到组中,但我需要将操作员 ID 分配给该组。
我认为我需要做这样的事情:
SELECT NTILE(2) OVER( order by orderID desc) as newID,*
FROM
orders(NOLOCK)
这将使我的订单表分成相等的部分。我需要知道的是运算符表的长度(将其作为参数添加到 NTILE),之后我可以将结果与运算符连接起来(使用row_number()
)
有更好的解决方案吗?
我的问题又来了:如何将结果集均等分组并使用另一个表数据更新该记录集?
EDIT:到目前为止,这是我的代码:http://sqlfiddle.com/#!3/39849/25 http://sqlfiddle.com/#!3/39849/25
EDIT 2我更新了我的问题并添加了更多条件。
我想根据某些条件将操作员分配给订单:
- 订单必须有现金>0
- 订单状态必须为 1
- 订单必须属于组 1 或组 2
- 操作员必须处于活动状态(活动=1)
- 操作员必须属于组 1 或组 2
我正在将此查询构建为存储过程。
因此,第一步是生成具有新分配的数据到临时表中,并在第二步最终批准后根据该临时表更新主表。
我还有 2 个问题:
是先将所有订单和满足条件的所有运算符选择到临时表中,然后进行改组还是在一个大查询中完成所有操作会更好?
我想将数组或组作为参数传递给我的过程。哪个选项最适合将数组传递给存储过程 (SQL Server 2005)。
我知道这个问题被问过很多次,但我想知道是否最好创建一个单独的函数来将逗号分隔的字符串剪切到表中(http://www.sommarskog.se/arrays-in-sql-2005.html http://www.sommarskog.se/arrays-in-sql-2005.html)或者把所有东西都放在一个大的程序中? :)
最终答案:可以在http://sqlfiddle.com/#!3/afb48/2 http://sqlfiddle.com/#!3/afb48/2
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.cash>0 and 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
答案基于戈登的利诺夫答案。谢谢!