SQL谜题-从TVP更新有序列表

Question

我急需这方面的线索。我是用C++CLI写这篇文章的，并使用ADO进行存储。我可以将逻辑放在C++端，也可以放在我调用的SQL Server 2012存储过程中。我有一个表，它表示一个物理（有序）的产品堆栈。不是一张大桌子，最多20件。我收到一个部分列表，在一条消息中最多有10个项目，显示前10个项目的重新排序。

首先，我必须验证消息中最多10个部分是否已经在我的表中。为此，我将包含项目及其顺序的TVP上传到存储过程中，并在存储过程中对EXCLUDE进行计数（*）。如果计数为零，那么我必须重新排序我的表，使TVP中最多10个取代我最多20个表中的任何顺序，然后保持任何剩余零件的顺序。

我想说，我已经尝试过一些东西，并抱怨错误，但我甚至无法理解起点。序列不起作用，因为它们不能包含order by子句。我不能做简单的交换，因为我有一张满是值的表，我不知道交换了什么（如果有的话）。我对t-SQL中的游标使用没有任何工作知识，所以我不知道从哪里开始。

我试过阅读CTE，没有什么明显的。序列太有限了。光标（如下）可以工作。有人有更好的解决方案吗？请注意，这些表的定义是我自己设定的，尽管表（此处为#TestTbl）具有检查约束，以确保Position在1和20之间，但它可以被删除，主键可以移动到同样唯一的外键IDA，IDB。

DROP TABLE #TestTbl;
GO
create table #TestTbl
(
    Position int not null,
    IDA int not null,
    IDB int not null,
    CONSTRAINT uc_FK UNIQUE (IDA, IDB),
    CONSTRAINT PK PRIMARY KEY (Position)
);
INSERT INTO #TestTbl (Position, IDA, IDB)
VALUES (1, 2, 3), (2, 3, 4), (3, 4, 5), (4, 5, 6), (5, 6, 7), (6, 6, 6);
SELECT * FROM #TestTbl
ORDER BY Position;
GO
DROP TABLE #TestTvp;
GO
create table #TestTvp
(
    Position int NOT NULL,
    IDA int NOT NULL,
    IDB int NOT NULL,
    CONSTRAINT PKA PRIMARY KEY (Position)
);
INSERT INTO #TestTvp (Position, IDA, IDB)
VALUES (1, 2, 3), (3, 3, 4), (2, 4, 5), (4, 5, 6)
SELECT * FROM #TestTvp
ORDER BY Position;
GO
drop table #NewTbl;
go
DECLARE @ID1 int;
DECLARE @ID2 int;
DECLARE @LastCount int;
DECLARE MyCurse CURSOR FOR
SELECT IDA, IDB
FROM #TestTbl
EXCEPT
SELECT IDA, IDB
FROM #TestTvp;
SELECT Position, IDA, IDB 
INTO #NewTbl
FROM #TestTvp;
SET @LastCount = @@ROWCOUNT;
OPEN MyCurse;
SET @LastCount = @LastCount + 1;
FETCH NEXT FROM MyCurse
INTO @ID1, @ID2;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #NewTbl (Position, IDA, IDB)
    VALUES (@LastCount, @ID1, @ID2);
    SET @LastCount = @LastCount + 1;
    FETCH NEXT FROM MyCurse
    INTO @ID1, @ID2;
END;
CLOSE MyCurse;
DEALLOCATE MyCurse;
SELECT * FROM #NewTbl
ORDER BY Position;
GO

Answer 1

我尝试过CTE+ROW_NUMBER方法：

declare @target table (
    ida int, idb int, position int)
insert @target values 
    (1, 1, 1), 
    (1, 2, 2),
    (1, 3, 3),
    (1, 4, 4),
    (2, 1, 5),
    (2, 2, 6)
declare @message table (
    ida int, idb int, position int)
insert @message values
    (2, 2, 1),
    (1, 2, 2)
;with result as (
    select 
        t.ida, t.idb, t.position, 
        row_number() over(order by p.prio asc, p.position asc) newPosition
    from @target t
    left join @message m on m.ida = t.ida and m.idb = t.idb
    cross apply (
        select 0 prio, m.position where m.position is not null union
        select 1 prio, t.position where m.position is null
    ) p
)
update result set position = newPosition
select * from @target order by position