SQL 在两表排列中向上或向下移动行

2021-09-10 00:00:00 sql tsql sql-server

我有两个以这种方式设计的表格，考虑到可能会重新排列元素:

I have two tables that I designed this way with a possible reshuffling of elements in mind:

1. [dbo.test_db_002] with columns:
[id] = INT NOT NULL IDENTITY(1,1) PRIMARY KEY
[name] = NVARCHAR(255)

和

2. [dbo.test_db_003] with columns:
[ord] = INT
[itmid] = INT NOT NULL PRIMARY KEY

[itmid] 列有一个约束，将其链接到 [dbo.test_db_002].[id]，如下所示:

[itmid] column has a constraint linking it to [dbo.test_db_002].[id] like so:

ALTER TABLE [dbo.test_db_003] 
ADD CONSTRAINT fk1 FOREIGN KEY ([itmid]) 
REFERENCES [dbo.test_db_002]([id]) 
ON DELETE CASCADE ON UPDATE CASCADE;

比如说，[dbo.test_db_002] 表有以下数据:

Say, [dbo.test_db_002] table has the following data:

[id] [name] 
3    John
5    Mary
8    Michael
10   Steve
13   Jack
20   Pete

和 [dbo.test_db_003] 具有以下排序数据:

and [dbo.test_db_003] has the following ordering data:

[ord] [itmid]
1      5
4      8
5      13
8      3
10     10
13     20

因此，当我从数据库中检索名称时，我使用以下 SQL:

So when I retrieve names from the database I use the following SQL:

SELECT [name]
FROM   [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 ON t1.[id]=t2.[itmid]
ORDER BY t2.[ord] ASC

它生成名称列表(按 [dbo.test_db_003].[ord] 列排序):

It produces the list of names (ordered by the [dbo.test_db_003].[ord] column):

Mary
Michael
Jack
John
Steve
Pete

我正在寻找一个选项，可以在列表中上下移动每个名称.例如，如果我想将John"上移一位，我该怎么做?

What I am looking for is an option to move each of the names up and down the list. For instance, if I want to move "John" one position up, what do I do?

到目前为止，我想出了这个部分 SQL:

So far I came up with this partial SQL:

WITH cte AS
(
    SELECT [id], [ord], ROW_NUMBER() OVER (ORDER BY t2.[ord] ASC) AS rowNum
    FROM [dbo.test_db_002] t1
    LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
)

这将选择以下内容:

rowNum  [id]  [ord]
1        1     5
2        4     8
3        5     13
4        8     3
5        10    10
6        13    20

所以我知道我需要将 [ord] 列中的值从索引 3 开始向上移动一个(因为John"索引是 4)，然后以某种方式将John"的 [ord] 设置为5，但是你是怎么做到的?

So I understand that I need to shift values in [ord] column up by one starting from the index 3 (since "John" index is 4) and then somehow make "John"'s [ord] to be set to 5, but how do you do that?

推荐答案

我准备了一个 完整演示 为您介绍这如何在 data.stackexchange.com 上工作.
该解决方案是根据您的评论量身定制的:

I prepared a complete demo for you how this can work on data.stackexchange.com.
The solution is tailored to your comment:

向上或向下移动只能是一步 - 换句话说，一个不能移动 2 个或更多位置

the move up or down can be only a single step - in other words, one cannot move 2 or more positions

在这个例子中，我让约翰与他上方的杰克交易顺序头寸:

In the example I make John trade ordinal positions with Jack above him:

WITH x AS (
  SELECT t2.itmid, t2.ord
  FROM   dbo.test_db_002 t1
  LEFT   JOIN dbo.test_db_003 t2 ON (t1.id = t2.itmid)
  WHERE  t1.name = 'John'  -- must be unique, or query by id ...
  )
  , y AS (
  SELECT TOP 1
         t.itmid, t.ord
  FROM   dbo.test_db_003 t, x
  WHERE  t.ord < x.ord     -- smaller ord = "above"
  ORDER  BY t.ord DESC
  )
UPDATE dbo.test_db_003 SET ord = z.ord
FROM (
   SELECT x.itmid, y.ord FROM x,y
   UNION ALL
   SELECT y.itmid, x.ord FROM x,y
   ) z
WHERE  dbo.test_db_003.itmid = z.itmid

###主要观点:

  1. 使用两个 CTE 来构建查询:
  2. 获取 John 的 id &顺序位置
  3. 对他上面的人也一样
  4. 在UNION ALL
    5. 的帮助下准备两行，其中这两个交换序数
  5. 在现在简单的UPDATE
    6. 中使用这两行
  1. Use two CTE to structure the query:
  2. Get John's id & ordinal position
  3. Get the same for the person above him
  4. Prepare two rows where these two switch ordinal numbers with the help of UNION ALL
  5. Use these two rows in a now simple UPDATE

  • 序号位置 ord 必须允许传递重复项才能使其工作.
  • 如果没有人在 'above'，则查询将无声无息地执行任何操作.

    • The ordinal position ord must allow passing duplicates for this to work.
    • If there is nobody 'above', the query will silently do nothing.

相关文章