Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Привет. Помогите с процедурой.

Есть такая таблица:
IdNo
15
23
31
42
54


Id - это первичный ключ. No это номер элемента - порядок, в котором их нужно выводить:

select Id from table order by No


Вопрос.
Как правильно написать процедуру, которая может перемещать элемент с одной позиции, в другую. При этом другие элементы должны также изменять свою позицию.

Т.е. представьте себе доминошки, которые лежать стопочкой. Если вы возьмем 3-тью доминошку (no=3) и положим в самый низ (no=1), то таблица изменится следующим образом:

IdNo
15
21
32
43
54


No3 становится No1, No1 стал No2, а No2 стал No3.
Диллема следующая: в зависимости от того, куда перемещается элемент - соседние элементы должны либо уменьшить свой No либо увеличить.
Если No3 должен стать No5, то No4 станет No3, а No5 станет No4.

Пока я думаю, что в этом деле может помочь MERGE. Но не могу придумать, как учесть именно этот момент - когда уменьшать, а когда увеличивать соседние элементы.

Как можно написать такую процедуру, с учетом транзакционности ! Чтобы никто не мог ворваться в этот процесс (например новая вставка) ? Помогите плиз.
26 апр 16, 17:29    [19109499]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21254
Ну перемещать надо, вероятно, используя таки UPDATE.
При перемещении "вниз" надо отнять по единичке от записей, что между начальным и конечным значениями (а вместо начального задать конечное).
При перемещении "вверх" надо, наоборот, добавить по единичке этим "промежуточным".
Соответственно в любом случае надо добавлять SIGN(начальное - конечное).
Получится что-то типа
UPDATE table
SET No = CASE No WHEN @before THEN @after ELSE No + SIGN(@before - @after) END
WHERE (No - @before) * (No - @after) <= 0

Это всё без учёта тонкостей, которые могут испоганить процесс...
26 апр 16, 17:48    [19109567]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina,

А нужно ли лочить таблицу на тот момент, когда вычисляем @before, @after и потом делает апдейт ?
Ведь если не заблочить, не получится ли, что-то пришедшая вставка сломает все ?
26 апр 16, 17:57    [19109599]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21254
ProBiotek
момент, когда вычисляем @before, @after

Что значит "вычисляем"? ты их сам задаёшь... то есть через клиента, но с точки зрения сервера это значения-литералы (ну или параметры функции, которые при её вызове тоже литералы).

ProBiotek
если не заблочить, не получится ли, что-то пришедшая вставка сломает все ?

Другой запрос влезет в процесс выполнения этого запроса?
26 апр 16, 18:08    [19109643]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina,

Извиняюсь. Я не совсем правильно описал проблему.
На вход процедура получает ID и NewNo.

Соответственно нужно сначала найти OldNo. А потом уже начинать все эти перемещения.


set @oldNo = (select No from table where id = @Id)

.... а дальше вот и делать все эти перемещения

UPDATE table
SET No = CASE No WHEN @before THEN @after ELSE No + SIGN(@before - @after) END
WHERE (No - @before) * (No - @after) <= 0
26 апр 16, 18:15    [19109666]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21254
Разумнее получение @oldNo спрятать в предложение WITH, чтобы у тебя был один запрос, а не два.
26 апр 16, 18:32    [19109707]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina,

Я правильно понял примерную идею?
(для простоты заменил @before и @after )
;with sss as (select No from table where id = @Id)
UPDATE table
SET No = CASE No WHEN sss.No THEN sss.No ELSE No + SIGN(sss.No - sss.No) END
WHERE (No - sss.No) * (No - sss.No) <= 0


Я тогда еще подумаю. Может быть благодаря WITH и CASE действительно получится решить данную задачку эффективно.
Идея нравится. Именно эту проблему я не знал как решить - объединить все в одну операцию, но без явного лока таблицы. Спасибо я запомню этот трюк :)
26 апр 16, 18:40    [19109734]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21254
ProBiotek
Я правильно понял примерную идею?

Ну типа да... только если такой выверт заработает, он может потребовать групповой функции (должен же он быть убеждён, что получит скалярное значение, а не набор записей) - ну типа
with sss as (select MAX(No) from table where id = @Id)

Правда, не знаю, насколько это корректно с точки зрения синтаксиса... может, пусть её будет таки таблица, типа
WITH temp(oldNo) 
AS select MAX(No) from table where id = @Id
UPDATE table
SET No = CASE table.No WHEN temp.oldNo THEN @newNo ELSE table.No + SIGN(temp.oldNo - @newNo) END
FROM table, temp
WHERE (No - temp.oldNo) * (No - @newNo) <= 0
26 апр 16, 18:48    [19109757]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina,

Прикольно. Вот я не знал, что так можно.
26 апр 16, 19:00    [19109802]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
declare @t table (id int primary key, n int);

insert into @t
values
 (1, 5),
 (2, 3),
 (3, 1),
 (4, 2),
 (5, 4);

declare @id int = 1, @move_to int = 2;

select * from @t order by n;

with s as
(
 select
  n, row_number() over (order by case when id = @id then @move_to else n end, case when id = @id then 0 else 1 end) as new_n
 from
  @t
)
update s
 set
  n = new_n
where
 n <> new_n;

select * from @t order by n;
26 апр 16, 19:08    [19109826]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
invm,

Блииин... Вот это да. Классно.
Я пока даже не понял как, но работает. Спасибо !
26 апр 16, 19:11    [19109839]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
invm,

Не работает все таки.

Если имеем такую таблицу.
И хотим переместить Id2 в No5 (сейчас он No3)

Id No
1 5
2 3
3 1
4 2
5 4


declare @t table (id int primary key, n int);

insert into @t
values
 (1, 5),
 (2, 3),
 (3, 1),
 (4, 2),
 (5, 4);

declare @id int = 2, @move_to int = 5;

select * from @t order by n;

with s as
(
 select
  n, row_number() over (order by case when id = @id then @move_to else n end, case when id = @id then 0 else 1 end) as new_n
 from
  @t
)
update s
 set
  n = new_n
where
 n <> new_n;

select * from @t order by n;


В вашем варианте получилось:


Id No
1 5
2 4
3 1
4 2
5 3


А должно было быть

Id, No
1, 4 << упал на одну позицию
2, 5 << переместился на No5
3, 1 << не изменился, т.к. ниже No3
4, 2 << не изменился, т.к. ниже No3
5, 3 << упал на одну позицию
26 апр 16, 20:15    [19110054]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
ProBiotek,

with s as
(
 select
  t.n, row_number() over (order by case when t.id = @id then @move_to else t.n end, case when t.id = @id then 0 else a.f end) as new_n
 from
  @t t cross apply
  (select n - @move_to from @t where id = @id) a(f)
)
update s
 set
  n = new_n
where
 n <> new_n;
26 апр 16, 20:53    [19110185]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
http://sqlfiddle.com даже не смог это прожевать, так что придется смотреть завтра, на работе

Спасибо.
26 апр 16, 21:07    [19110235]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Работает. Круто.
27 апр 16, 11:03    [19111678]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
invm,

Разрешите еще вопросик.
Я немного невнимательно изучил структуру данных для задачи. В общем данные выглядят так:

IdNo Group
1 5 1
2 3 1
3 1 1
4 2 1
5 4 1
6 5 2
7 3 2
8 1 2
9 2 2
10 4 2


На вход, все также, поступает ID и NO. Но нужно выяснить Группу, к которой относится заданный ID, и поменять местами в пределах этой группы !

Т.е. для приведенной таблицы, если на вход поступило Id=8 No =5, то и перестановки должны произойти только в пределах группы 2.

Я бы мог сделать так:

Set @Group = (select Group from t where id = @id)

...

@t t cross apply
  (select n - @move_to from @t where id = @id and Group = @Grpoup) a(f)


Но опять же, данный код не потоко-безопасен.


Пробовал сделать и так:

with g as 
(
  select t.Group
  from  t t
  where t.Id = @Id
),
s as
(
 select
...

from t cross apply
  (select [No] - @no from t where id = @id and Group=g.Group) a(f)


Но ругается, что g.Group неизвестно в данной точке

Помогите плиз еще раз.
27 апр 16, 15:53    [19113418]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Не очень понимаю, почему g.Group не известно в данной точке программы. Ведь это CTE, которое объявлено выше - должно быть видно.
27 апр 16, 15:55    [19113423]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
ProBiotek,

потому что надо g объявить во FROMе
27 апр 16, 15:58    [19113455]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Сделал так, и заработало ! Но насколько данное решение корректное ?

with g as 
(
  select t.Group
  from  t t
  where t.Id = @Id
),
s as
(
 select g.Group

...

from g, t cross apply
  (select [No] - @no from t r where id = @id and r.Group=Group) a(f)
27 апр 16, 15:59    [19113459]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
ProBiotek,

вообще некорректное.
r.Group=Group
возвращает всегда TRUE, если Group IS NOT NULL, конечно.
И CROSS JOIN (запятая то есть)... У нас за такое выводили водвор и расстреливали.
О запятой вообще лучше забыть.
27 апр 16, 16:04    [19113500]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
В общем вот финальная версия кода, на которой застрял. Он не компилится даже. Из-за этой ссылки на поле из другого CTE

declare @t table (id int primary key, n int, gr int);

insert into @t
values
 (1, 5,1),
 (2, 3,1),
 (3, 1,1),
 (4, 2,1),
 (5, 4,1),
 (6, 5,2),
 (7, 3,2),
 (8, 1,2),
 (9, 2,2),
 (10, 4,2);

declare @id int = 1, @move_to int = 2;

select * from @t order by n;

with g as 
(
  select t.gr
  from  @t t
  where t.Id = @Id
),
s as
(
 select
  t.n, row_number() over (order by case when t.id = @id then @move_to else t.n end, case when t.id = @id then 0 else a.f end) as new_n
 from g,
  @t t cross apply
  (select n - @move_to from @t r where id = @id and r.gr = g.gr) a(f)
)
update s
 set
  n = new_n
where
 n <> new_n;

select * from @t order by n;
27 апр 16, 16:06    [19113520]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
iap
ProBiotek,

вообще некорректное.
r.Group=Group
возвращает всегда TRUE, если Group IS NOT NULL, конечно.
И CROSS JOIN (запятая то есть)... У нас за такое выводили водвор и расстреливали.
О запятой вообще лучше забыть.


Не очень понял, о какой запятой идет речь.
27 апр 16, 16:07    [19113531]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
ProBiotek
iap
ProBiotek,

вообще некорректное.
r.Group=Group

возвращает всегда TRUE, если Group IS NOT NULL, конечно.
И CROSS JOIN (запятая то есть)... У нас за такое выводили водвор и расстреливали.
О запятой вообще лучше забыть.


Не очень понял, о какой запятой идет речь.
Во FROMе
27 апр 16, 16:08    [19113540]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
invm,

Можете подсказать, как добавить обработку групп к решению ?
27 апр 16, 18:17    [19114237]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать процедуру.  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
ProBiotek
как добавить обработку групп к решению ?
with s as
(
 select
  t.n, row_number() over (order by case when t.id = @id then @move_to else t.n end, case when t.id = @id then 0 else a.f end) as new_n
 from
  (select n - @move_to, g from @t where id = @id) a(f, g) join
  @t t on t.g = a.g
)
update s
 set
  n = new_n
where
 n <> new_n;
27 апр 16, 18:26    [19114263]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить