mysql - 通过多个组排名更新

Question

基本问题

所以我正在创建一项服务，让客户注册并接收神秘玩具。客户给我们成批的订单（每个订单都有自己的价格限制），然后我们尝试将每个单独的订单与我们认为最好的玩具配对，该玩具也在该订单的价格限制之下。

客户可以更新他们的偏好以缩小我们发送给他们的潜在玩具；并非每个客户都会对我们提供的每种玩具感兴趣。当然，他们不希望我们将同一个玩具寄给他们两次。

问题

如果我有两张表：PotentialToys（列出哪些玩具可以卖给哪些客户，以及它们的价格并按估计值排名）和 ToyOrders（其中包含客户、每个订单的价格限制以及我们计划的玩具）给它们（默认为空，在 UPDATE 上设置）），我如何更新表 ToyOrders 中的订单以选择既低于该订单价格限制且每个客户只选择一次的玩具？

MySQL 模式示例

CREATE TABLE PotentialToys (
    customer   NVARCHAR(25) NOT NULL,
    toy        NVARCHAR(25) NOT NULL,
    price      DECIMAL(5,2) NOT NULL,
    rank       INTEGER NOT NULL
 );

 CREATE TABLE ToyOrders (
   order_id     INTEGER PRIMARY KEY AUTO_INCREMENT,
   customer     NVARCHAR(25) NOT NULL,
   price_limit  DECIMAL(5,2) NOT NULL,
   rank         INTEGER NOT NULL,
   selected_toy NVARCHAR(25)
 );

 INSERT INTO PotentialToys (customer, toy, price, rank) VALUES 
 ('Alice', 'Chemistry Kit', 20.00, 1),
 ('Alice', 'Stuffed Bear', 5.00, 2),
 ('Alice', 'Playing Cards', 10.00, 3),
 ('Bob', 'Playstation', 500.00, 1),
 ('Bob', 'Baseball Monkey', 25.00, 2),
 ('Bob', 'Stuffed Bear', 5.00, 3),
 ('Bob', 'Chemistry Kit', 20.00, 4),
 ('Carlos', 'Magic Trick Set', 50.00, 1),
 ('Carlos', 'Playing Cards', 10.00, 2),
 ('Carlos', 'Banana-Phone', 20.00, 3);

 INSERT INTO ToyOrders (customer, price_limit, rank) VALUES 
 ('Alice', 20.00, 1),
 ('Alice', 15.00, 2),
 ('Alice', 10.00, 3),
 ('Bob', 25.00, 1),
 ('Bob', 25.00, 2),
 ('Bob', 15.00, 3),
 ('Carlos', 25.00, 1);

更新 ToyOrders 后所需的输出

SELECT order_id, customer, price_limit, selected_toy FROM ToyOrders;

+-------------------------------------------------------------
  | order_id | customer | price_limit    | selected_toy    |
+-------------------------------------------------------------
  | 1        | Alice    |  $20.00        | Chemistry Kit   |
  | 2        | Alice    |  $15.00        | Stuffed Bear    |
  | 3        | Alice    |  $10.00        | Playing Cards   |
  | 4        | Bob      |  $25.00        | Baseball Monkey |
  | 5        | Bob      |  $25.00        | Stuffed Bear    |
  | 6        | Bob      |  $15.00        | NULL            | -- No toys left for Bob under $15. It's OK to leave NULL
  | 7        | Carlos   |  $25.00        | Playing Cards   | -- The magic trick set cost more than the order's price limit, so he gets the next best thing

到目前为止我已经尝试过：

SET @offset_rank = 0;
UPDATE ToyOrders AS O 
INNER JOIN PotentialToys AS PT 
    ON O.customer = PT.customer
    AND O.rank + @offset_rank = PT.rank 
    AND IF(PT.price <= O.price_limit, TRUE, (@offset_rank := @offset_rank + 1) && FALSE)
SET O.selected_toy = PT.toy;

以上只为爱丽丝产生正确的输出；Bob 和 Carlos 最终收到了错误的包裹，如下所示：

+-------------------------------------------------------------
  | order_id | customer | price_limit    | selected_toy    |
+-------------------------------------------------------------
  | 1        | Alice    |  $20.00        | Chemistry Kit   |
  | 2        | Alice    |  $15.00        | Stuffed Bear    |
  | 3        | Alice    |  $10.00        | Playing Cards   |
  | 4        | Bob      |  $25.00        | NULL            | -- Should be Baseball Monkey, not NULL
  | 5        | Bob      |  $25.00        | Stuffed Bear    |
  | 6        | Bob      |  $15.00        | NULL            | 
  | 7        | Carlos   |  $25.00        | Banana-Phone    | -- Should be Playing Cards

我应该做些什么不同的事情？

标签： mysqlsql