ADOQuery，SQL语句和数据库中行的重新排序

I have a database Table Named P_Columns. This Table Contains the columns

我有一个名为P_Columns的数据库表。此表包含列

ID
Column_Name
Column_Type
Column_Visible
Column_Order

This Data table gets populated and updated by an Application which displays a dbgrid. This application has a button. that when clicked it takes the selected row. and moves it up one row (This is where I need help).

此数据表由显示dbgrid的应用程序填充和更新。这个应用程序有一个按钮。单击时，它将获取所选行。并将其向上移动一行（这是我需要帮助的地方）。

Now my initial SQL Statement (SELECT * FROM P_Columns Order by Column_Order asc) sets the correct order. initially.

现在我的初始SQL语句（SELECT * FROM P_Columns Order by Column_Order asc）设置正确的顺序。原来。

I need to find out if there is an update Sql statement that will allow me to swap the Column_Order of the selected record. with that of the record just before it.

我需要找出是否有一个更新Sql语句，它将允许我交换所选记录的Column_Order。与之前的记录相同。

Hope this makes sense.

希望这是有道理的。

4 个解决方案

#1

Here is Delphi code that does what you want using TADOCommand calling an update statement twice. This is tested against MS SQL Server. If you do not use TADO* components you should be able to convert to whatever database components you use. ADODataSet1 is the data that is presented in the grid.

这是使用TADOCommand调用更新语句两次执行所需的Delphi代码。这是针对MS SQL Server进行测试的。如果您不使用TADO *组件，您应该能够转换为您使用的任何数据库组件。 ADODataSet1是网格中显示的数据。

var
    CurrentID: Integer;
    CurrentOrder: Integer;
    PrevID: Integer;
    PrevOrder: Integer;
begin
    if ADODataSet1.RecNo > 1 then // Do not move the first row
    begin
        CurrentID := ADODataSet1['ID'];
        CurrentOrder := ADODataSet1['Column_Order'];

        ADODataSet1.Prior;
        PrevID := ADODataSet1['ID'];
        PrevOrder := ADODataSet1['Column_Order'];

        ADOCommand1.CommandText := 'update P_Columns set Column_Order = :Column_Order where ID = :ID';

        ADOCommand1.Parameters.ParamByName('Column_Order').Value := PrevOrder;
        ADOCommand1.Parameters.ParamByName('ID').Value := CurrentID;
        ADOCommand1.Execute;

        ADOCommand1.Parameters.ParamByName('Column_Order').Value := CurrentOrder;
        ADOCommand1.Parameters.ParamByName('ID').Value := PrevID;
        ADOCommand1.Execute;

        ADODataSet1.Requery([]);
    end;
end;

#2

I believe this will do it for you...

我相信这会为你做到......

update P_Columns 
set Column_Order = ((select count(*) from P_Columns) + 1) - Column_Order 

#3

Swap just two positions. The last AND :order > 1 is used so that position 1 cannot be swapped with position 0 (non-existent)

交换两个位置。使用最后一个AND：order> 1，以便位置1不能与位置0交换（不存在）

update datatable
set column_order = case 
                      when column_order = :order then column_order-1 
                      else column_order+1 
                   end
where column_order in (:order, :order-1)
  and :order > 1

The CASE statement should exist in most major DBMS.

CASE语句应存在于大多数主要DBMS中。

Note: there is a bug in (stock) TADOQuery that will not set the parameter all 4 :order parameters in one go. 4 parameters will be required all carrying the same value, unless the DBMS is known to optimize the query for a specific DBMS.

注意：（stock）TADOQuery中存在一个错误，它不会一次性设置参数all 4：order parameters。除非已知DBMS优化特定DBMS的查询，否则将需要4个参数全部携带相同的值。

#4

I may be wrong, but it seems like in this case you don't have to have one statement only solution. Neither do I see it implied, as you were only talking about an UPDATE statement, which doesn't mean you cannot have any other statements preparing your update, does it? But if you did have that in mind, forgive me, please, yet I am going to move on. :)

我可能错了，但似乎在这种情况下你不必只有一个声明解决方案。我也没有看到它暗示，因为你只是在谈论UPDATE声明，这并不意味着你不能有任何其他声明准备你的更新，是吗？但如果你确实考虑到了这一点，请原谅我，但我会继续前进。 :)

First, I would like to know if your Column_Order has indeed no gaps. Because if there's no gaps and the values start from 1, then you can get away with cyberkiwi's solution with only this modification (yet all credit should go to that person):

首先，我想知道您的Column_Order确实没有间隙。因为如果没有间隙并且值从1开始，那么你可以通过这种修改来逃避cyberkiwi的解决方案（但是所有的功劳都归功于那个人）：

declare @order int;
set @order = :order;

update P_Columns
set Column_Order =
  case 
    when Column_Order = @order then Column_Order - 1 
    else Column_Order + 1 
  end
where Column_Order in (@order, @order - 1) and @order > 1

That is, you need to declare a @var so you only use :order once per script. (You may probably have known that already.) And there you are.

也就是说，您需要声明一个@var，因此您只能使用：每个脚本订购一次。 （你可能已经知道了。）而且你就是。

However, if that solution cannot be applied as is, then basically, I think, you just need to add more statements to calculate beforehand the other order value due to be swapped, then update.

但是，如果该解决方案不能按原样应用，那么基本上，我认为，您只需要添加更多语句来预先计算由于交换的其他订单值，然后更新。

Here's what it can be like:

这就是它的样子：

declare @order int, @prev_order int;
set @order = :order;

/* here goes looking up for the other Column_Order */
select @prev_order = max(Column_Order)
from P_Columns
where Column_Order < @order;

/* and now update, which is basically the same,
   only adapted for use with @prevorder,
   and also we check if @prevorder has a value */
if @prevorder is not null
  update P_Columns
  set Column_Order =
    case Column_Order
      when @order then @prevorder
      else @order
    end
  where Column_Order in (@order, @prevorder)

If you have any questions, they are welcome.

如果您有任何疑问，欢迎他们。