如何在`delete from ... into`查询中增加次序号?
问题描述:
假设我是一个萌芽的口袋妖怪卡收集器,并且我使用SQL数据库来跟踪我的卡片。我给Pokemon的每个种类分配一个号码,id
。我想跟踪订单我收集每种类型,所以我也为每张卡分配一个序列号seqno
。我在货架上展示这些卡片。如何在`delete from ... into`查询中增加次序号?
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
...然后使用-1
提醒我要设置序列号,但我厌倦了手动设置。
我也是一个非常type-A编码器,我不想编写第二个查询来更新序列号。我希望这一切都发生在“从包到货架”的查询中。
我知道这些限制听起来很奇怪。我试图提炼出一个更复杂的问题,所以希望这个故事有所帮助。
答
更改您的删除到插入然后删除。使用子查询从货架中查找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
答
窗口功能
select id
, row_number over (partition by id, order by id) as autoSeqno
from shelf
或只是使用包的基表,并不要删除它
你的第一次插入到架子看起来像2卡,你说它是第一种和第二种之一第二?但它看起来像第一个3和第二个2。另外他们为什么分享seqnum?不应该更像'(0,1)(1,2)(1,3)'?还是你想seqnum只增加每个ID?无论哪种方式,我很困惑5插入= 3卡(我得到2个ID) –
不要从'bag'中删除。添加一个'shelfId'列。袋子包含那些不是'NULL'的。 –
@Aaron Dietz你是对的,第一个3,第二个2,感谢你的支持。是的,我只希望seqnum每个ID增加,而不是整个表。 – mac9416