问题描述
我有两个以这种方式设计的表格,考虑到可能会重新排列元素:
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
###主要观点:
- 使用两个 CTE 来构建查询:
- 获取 John 的 id &顺序位置
- 对他上面的人也一样
- 在UNION ALL 的帮助下准备两行,其中这两个交换序数
- 在现在简单的UPDATE 中使用这两行
- Use two CTE to structure the query:
- Get John's id & ordinal position
- Get the same for the person above him
- Prepare two rows where these two switch ordinal numbers with the help of UNION ALL
- 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.