問題描述

我有兩個以這種方式設(shè)計的表格，考慮到可能會重新排列元素:

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] 表有以下數(shù)據(jù):

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] 具有以下排序數(shù)據(jù):

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

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

因此，當我從數(shù)據(jù)庫中檢索名稱時，我使用以下 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]
)

這將選擇以下內(nèi)容:

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] 設(shè)置為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 上工作.
該解決方案是根據(jù)您的評論量身定制的:

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 來構(gòu)建查詢:
2. 獲取 John 的 id &順序位置
3. 對他上面的人也一樣
4. 在UNION ALL
的幫助下準備兩行，其中這兩個交換序數(shù)
5. 在現(xiàn)在簡單的UPDATE
中使用這兩行

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 必須允許傳遞重復(fù)項才能使其工作.
• 如果沒有人在 'above'，則查詢將無聲無息地執(zhí)行任何操作.
• The ordinal position ord must allow passing duplicates for this to work.
• If there is nobody 'above', the query will silently do nothing.

這篇關(guān)于SQL 在兩表排列中向上或向下移動行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！