SQL для перемещения строк вверх или вниз при расположении в двух таблицах

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

У меня есть две таблицы, которые я разработал таким образом с учетом возможной перетасовки элементов:

 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] вот так:

 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] содержит следующие данные:

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

и [dbo.test_db_003] содержит следующие упорядоченные данные:

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

Поэтому, когда я извлекаю имена из базы данных, я использую следующий 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]).:

 Mary
Michael
Jack
John
Steve
Pete

То, что я ищу, — это возможность перемещать каждое из имен вверх и вниз по списку. Например, если я хочу переместить «John» на одну позицию вверх, что мне делать?

Пока я придумал этот частичный 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), а затем каким-то образом установить значение [ord] для «John» равным 5, но как вы это делаете?

1. Каким будет процесс, с помощью которого имя перемещается вверх или вниз по списку? Обновляется ли таблица упорядочения или какое-либо другое действие вызовет изменение?

2. Да, мне нужно придумать инструкцию UPDATE, чтобы обновить таблицу упорядочения, т. е. [dbo.test_db_003]. столбец [ord]. Также перемещение вверх или вниз может быть только одним шагом — другими словами, нельзя переместить 2 или более позиций…

Ответ №1:

Я подготовил для вас полную демонстрацию того, как это может работать на data.stackexchange.com .
Решение адаптировано к вашему комментарию:

перемещение вверх или вниз может быть только одним шагом — другими словами, нельзя переместить 2 или более позиций

В примере я заставляю Джона менять порядковые позиции с Джеком над ним:

 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 для структурирования запроса:
Получить идентификатор Джона и порядковую позицию
Получите то же самое для человека над ним
Подготовьте две строки, в которых эти две переключают порядковые номера с помощью UNION ALL
Используйте эти две строки в теперь простом UPDATE

Порядковая позиция ord должна позволять передавать дубликаты, чтобы это работало.
Если нет никого «выше», запрос автоматически ничего не сделает.