sql - 更新语句以重新排序表队列

Question

有一张表，测试数据如下

PKId         EqId    Worker     Queue
1             1      Worker1      1
3             1      Worker3      3
2             1      Worker2      4
4             1      Worker4      7 

需要更新将轮换 Workers 的表，以便在更新语句之后数据如下所示

PKId         EqId   Worker  Queue
1             1     Worker1   7
3             1     Worker3   1
2             1     Worker2   3
4             1     Worker4   4 

Answer 1

您几乎可以通过连接来做到这一点:

select pkid, eqid, worker, queue, tprev.queue as newqueue
from t left outer join
     t tprev
     on t.pkid = tprev.pkid + 1

但是，对于 PkID = 1，您最终会得到 NULL。因此，我们需要将其添加到:

select pkid, eqid, worker, queue, coalesce(tprev.queue, lastone.queue) as newqueue
from t left outer join
     t tprev
     on t.pkid = tprev.pkid + 1 cross join
     (select top 1 queue from t order by pkid desc) as lastone

如果你真的想更新，那么我们可以把它放在一个更新语句中:

update t
    set queue = newqueue
    from (select pkid, eqid, worker, queue,
                 coalesce(tprev.queue, lastone.queue) as newqueue
          from t left outer join
               t tprev
               on t.pkid = tprev.pkid + 1 cross join
               (select top 1 queue from t order by pkid desc) as lastone
         ) newt
    where t.pkid = newt.pkid

我意识到您的主键可能有漏洞。在这种情况下，从以下查询开始:

select t.*, newQueue
from (select t.*,
             (select top 1 queue from t t2 where t2.pkid < t.pkid order by t2.pkid desc
             ) as newQueue
      from t
     ) t

在更新语句中:

update t
    set queue = newqueue
    from (select pkid, eqid, worker, queue,
                 coalesce(Newqueue, lastone.queue) as newqueue
          from (select t.*,
                       (select top 1 queue from t t2 where t2.pkid < t.pkid order by t2.pkid desc
                       ) as newQueue
                from t
               ) t cross join
               (select top 1 queue from t order by pkid desc) as lastone
        ) newt
    where t.pkid = newt.pkid