如何在一个查询中获取一项和另一项的值最接近前一项的项？

Question

想象一下我有下表：（

ID || Order
-----------
1  || 1
2  || 2
3  || 5
4  || 20
5  || 100
6  || 4000

没有特定规则适用于订单值）。

我想通过交换订单值来“向上移动”或“向下移动”项目。

例如：调用 MoveItemUp(4) 将产生此新表值：

ID || Order
-----------
1  || 1
2  || 2
3  || 20   <-- swapped order value
4  || 5    <-- swapped order value
5  || 100
6  || 4000

我想在单个查询中执行此操作，但尚未成功。

如果项目顺序是连续的且没有“漏洞”（步骤为 1：），则以下查询有效。

UPDATE dbo.ITEMS
set ORDER = case when c.ORDER = c2.ORDER  then c.ORDER  +1 else c.ORDER -1 end

from dbo.ITEMS c
    inner join dbo.ITEMS c2 on c.ORDER   = c2.ORDER   or c.ORDER  = c2.ORDER  + 1
where c2.ID=4

但是，我无法更改此查询以支持漏洞。我正在尝试这样做：

UPDATE dbo.ITEMS
    set case when c.ORDER  = c2.ORDER then min(c2.ORDER ) else c2.ORDER   end
FROM dbo.ITEMS c
    inner join ITEMS c2 on c2.ORDER >= c.ORDER 
    where c2.ID=4
group by c.CAT_ID, c.ORDER 
having c.ORDER = min(c2.ORDER ) or c.ORDER  = c2.ORDER 

但是，这并不按预期工作（查询更新具有更大顺序的所有项目，而不是要交换的两个顺序）。

PS：我正在 Sybase ASE 4.5 上使用 C# 2.0，但我认为这个问题不是特定于该平台的。如果您有 MSSQL、MySql 或 Oracle 同等产品，我会尽力将其转换；）

Answer 1

注意以下所有解决方案都假设 ItemOrder 是唯一的

编辑添加一个更像OP尝试的解决方案，并且可能更容易移植到Sybase，这次是在Microsoft SQL Server上2008。（请参阅下面使用 Oracle 分析函数的解决方案，如果可用的话可能会更有效。）

首先选择以使我们的行选择标准正确：

declare @MoveUpId int
set @MoveUpId = 4

select current_row.Id
    , current_row.ItemOrder
    , prior_row.id as PriorRowId
    , prior_row.ItemOrder as PriorItemOrder
    , next_row.id as NextRowId
    , next_row.ItemOrder as NextItemOrder
from #Items current_row
left outer join #Items prior_row
    on prior_row.ItemOrder = (select max(ItemOrder) 
        from #Items 
        where ItemOrder < current_row.ItemOrder)
left outer join #Items next_row 
    on next_row.ItemOrder = (select min(ItemOrder) 
        from #Items 
        where ItemOrder > current_row.ItemOrder)
where @MoveUpId in (current_row.id, next_row.id)

然后根据上述进行更新：

update current_row
set ItemOrder = case 
    when current_row.Id = @MoveUpId then prior_row.ItemOrder
    else next_row.ItemOrder end
from #Items current_row
left outer join #Items prior_row
    on prior_row.ItemOrder = (select max(ItemOrder) 
        from #Items 
        where ItemOrder < current_row.ItemOrder)
left outer join #Items next_row 
    on next_row.ItemOrder = (select min(ItemOrder)
        from #Items 
        where ItemOrder > current_row.ItemOrder)
where @MoveUpId in (current_row.id, next_row.id)

Id  ItemOrder
1   1
2   2
3   20
4   5
5   100
6   4000
10  -1
20  -2

设置@MoveUpId到20 并重新运行上面的查询结果：

Id  ItemOrder
1   1
2   2
3   20
4   5
5   100
6   4000
10  -2
20  -1

但我认为这个问题不是特定于该平台的。问题可能不具体，但答案可能是。例如，首先使用 Oracle，一个表和一些测试数据：

create table Items (Id number(38) not null
    , ItemOrder number);

insert into items values (1, 1);
insert into items values (2, 2);
insert into items values (3, 5);
insert into items values (4, 20);
insert into items values (5, 100);
insert into items values (6, 4000);
insert into items values (10, -1);
insert into items values (20, -2);
commit;

接下来创建一个查询，仅返回我们想要使用 Order 的新值更新的行。 （我将其命名为 ItemOrder，Order 是保留字等。）在 Oracle 中，使用分析函数 lag 和 lead 是最简单的：

select *
from (select Id
        , ItemOrder
        , lead(Id) over (order by Id) as LeadId
        , lead(ItemOrder) over (order by Id) as LeadItemOrder
        , lag(ItemOrder) over (order by Id) as LagItemOrder
    from Items)
where 4 in (Id, LeadId)
order by Id;

        ID  ITEMORDER     LEADID LEADITEMORDER LAGITEMORDER
---------- ---------- ---------- ------------- ------------
         3          5          4            20            2
         4         20          5           100            5

将其转换为更新语句。然而，上面的查询不会创建可更新的视图（在 Oracle 中），因此请使用合并：

merge into Items TRGT
using (select Id
        , ItemOrder
        , lead(Id) over (order by Id) as LeadId
        , lead(ItemOrder) over (order by Id) as LeadItemOrder
        , lag(ItemOrder) over (order by Id) as LagItemOrder
    from Items) SRC
on (SRC.Id = TRGT.Id)
when matched then update 
set ItemOrder = case TRGT.Id 
    when 4 then SRC.LagItemOrder 
    else SRC.LeadItemOrder end
where 4 in (SRC.Id, SRC.LeadId);

select * from Items order by Id;

        ID  ITEMORDER
---------- ----------
         1          1
         2          2
         3         20
         4          5
         5        100
         6       4000
        10         -1
        20         -2

不幸的是，我不相信滞后和超前被广泛实现。据我所知，Microsoft SQL Server 尚未实现它们。没有使用 ASE 的经验，但他们的产品很棒。

Row_number() 得到了更广泛的实现。 Row_number() 可用于获取无间隙的内容。 （Row_number() 在 Oracle 上被称为分析函数，在 SQL Server 上被称为窗口函数。）首先是查询：

with t as (select Id
        , ItemOrder
        , row_number() over (order by Id) as RN
    from Items)
select current_row.id
    , current_row.ItemOrder
    , next_row.Id as NextId
    , next_row.ItemOrder NextItemOrder
    , prior_row.ItemOrder PriorItemOrder
from t current_row
left outer join t next_row on next_row.RN = current_row.RN + 1
left outer join t prior_row on prior_row.RN = current_row.RN - 1
where 4 in (current_row.id, next_row.id);

        ID  ITEMORDER     NEXTID NEXTITEMORDER PRIORITEMORDER
---------- ---------- ---------- ------------- --------------
         3          5          4            20              2
         4         20          5           100              5

执行更新，再次使用合并而不是更新。 （Oracle 确实允许 update ... from ... join ... 语法，在其他平台上可能可以使用更新而不是合并。）

merge into Items TRGT
using (with t as (select Id
            , ItemOrder
            , row_number() over (order by Id) as RN
        from Items)
    select current_row.id
        , current_row.ItemOrder
        , next_row.Id as NextId
        , next_row.ItemOrder as NextItemOrder
        , prior_row.ItemOrder as PriorItemOrder
    from t current_row
    left outer join t next_row on next_row.RN = current_row.RN + 1
    left outer join t prior_row on prior_row.RN = current_row.RN - 1
    where 4 in (current_row.id, next_row.id)) SRC
on (TRGT.Id = SRC.Id)
when matched then update 
set ItemOrder = case 
    when TRGT.Id = 4 then SRC.PriorItemOrder 
    else SRC.NextItemOrder end;

select *
from Items
order by Id;

        ID  ITEMORDER
---------- ----------
         1          1
         2          2
         3         20
         4          5
         5        100
         6       4000
        10         -1
        20         -2

注意 请注意，如果与第一行的 Id 匹配，上面的解决方案将在 OrderItems 上写入 null。