如何在“从.删除”中增加次要序列号。输入查询？

Question

假设我是一个新兴的口袋妖怪卡收集器，我使用一个SQL数据库来跟踪我的卡片。我给每种口袋妖怪分配一个数字，id。我想要跟踪我收集到的每一种订单，所以我也给每一张卡分配了一个序列号seqno。我把卡片陈列在架子上。

create table shelf (
    kind_id int not null,
    seqno int not null
)

insert into shelf values (0, 1), 
                         (0, 2),
                         (0, 3),
                         (1, 1),
                         (1, 2)

我只抓到两种口袋妖怪:第一类三种，第二种两种。

当我去收集更多的卡片时，我把它们扔进一个袋子里。

create table bag (
    kind_id int not null,
)

insert into bag values (0),
                       (0),
                       (1),
                       (2)  -- woot, caught a new one

很容易把我所有的卡片从袋子里搬到架子上.

delete from bag
output deleted.kind_id, -1
into shelf (kind_id, seqno)
select * from bag 

然后，...and使用-1提醒我设置序列号，但我厌倦了手动设置它。

我也是一个非常类型的编码器，我不想写第二个查询来更新序列号。我希望这一切发生在“从一个袋子到另一个货架”的查询。

约束听起来很奇怪，我知道。我正试图找出一个更复杂的问题，希望这个故事能有所帮助。

Answer 1

将删除-改为插入，然后删除。使用子查询从货架中查找MAX(seqnum) (与正确的id相关)，并在插入期间增加它。将子查询包装在ISNULL('',1)中，以便为新卡在1处启动seqnum (或在seqnum列上使用默认值约束)：

SELECT b.kind_id, 
       ISNULL(seqno + ROW_NUMBER() OVER (PARTITION BY b.kind_id ORDER BY b.kind_id),1)
FROM bag b
LEFT JOIN (SELECT MAX(seqno) seqno, kind_id
           FROM shelf
           GROUP BY kind_id) s on s.kind_id = b.kind_id

然后只有DELETE FROM bag

Answer 2

窗口函数

select id 
     , row_number over (partition by id, order by id) as autoSeqno 
from shelf  

或者只需将包用于基表，而不删除它