在数据库中“上下”移动行（SQL 查询帮助）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4101513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Moving rows 'up and down' in a database (SQL query help)
提问by Chris
In the table I want to be able to 'move' rows, I have a column called sortid, which automatically on insert is one value higher than the highest value (easily found by selecting top value when sorting my sortid desc).
在我希望能够“移动”行的表中，我有一个名为 sortid 的列，它在插入时自动比最高值高一个值（通过在对 sortid desc 进行排序时选择最高值很容易找到）。
But I'm a bit confused on what query I'd use for the the 'up/down' actions for each row on my ASP.NET page. What query would I use to select the rows immediately 'below' or 'above' the row to be moved?
但是我对 ASP.NET 页面上每一行的“向上/向下”操作使用什么查询感到有些困惑。我将使用什么查询来选择要移动的行“下方”或“上方”的行？
Thanks
谢谢
回答by Guffa
Moving a record up or down is done by swapping it with the record before or after, respectively.
向上或向下移动记录是通过分别将其与之前或之后的记录交换来完成的。
If the SortIdvalues are always continuous (i.e. you don't remove records which would cause a gap), then you can just add or subtract one to get the next or previous record. If the records are not guaranteed to be continuous, then you have to find the record next to the one that you want to move.
如果SortId值始终是连续的（即您不删除会导致间隙的记录），那么您只需添加或减去一个即可获得下一条或上一条记录。如果不能保证记录是连续的，那么您必须在要移动的记录旁边找到该记录。
To find the SortIdof the record before:
要查找SortId之前的记录：
select max(SortId) from TheTable where SortId < @SortId
To find the SortIdof the record after:
要找到SortId之后的记录：
select min(SortId) from TheTable where SortId > @SortId
To swap the two records, you can use this trick that calculates one value from the other:
要交换两条记录，您可以使用此技巧从另一个计算一个值：
update TheTable
set SortId = @SortId1 + @SortId2 - SortId
where SortId in (@SortId1, @SortId2)
回答by Daniel Vassallo
One option is to increment all the sortidfields of the rows "below" just before INSERTing the new row. You can do this with an UPDATEcommand immediately followed by an INSERTcommand. Let's say you're inserting a row at position 10. Then you can do something like this:
一种选择是在新行sortid之前INSERT增加“下面”行的所有字段。你可以用一个UPDATE命令紧跟一个命令来做到这一点INSERT。假设您要在位置 10 处插入一行。然后您可以执行以下操作：
UPDATE your_table SET sortid = sortid + 1 WHERE sortid >= 10;
INSERT INTO your_table (..., sortid) VALUES (..., 10);
You should probably wrap that up in a transactionto ensure that the operation is carried out atomically.
As for the up/down actions, you wouldn't want to do an INSERT. The above is only appropriate for adding newrows at a particular "location". For the up/down action, you can simply "swap" the sortidvalue of the two rows, as @Guffa suggested in the other answer.
至于向上/向下操作，您不会想要执行INSERT. 以上仅适用于在特定“位置”添加新行。对于向上/向下操作，您可以简单地“交换”sortid两行的值，正如@Guffa 在另一个答案中所建议的那样。
回答by jordanbtucker
For getting the rows above the target row.
用于获取目标行上方的行。
select * from [TableName]
where sortid > (select sortid from [TableName]
where id = @id)
For the ones below, just use <instead.
对于下面的，只需使用<。
