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

Откуда:
Сообщений: 7
Здравствуйте! Помогите пожалуйста с решением нетривиальной пока что для меня такой задачи.
Есть таблица вида
ID Family Name
1ПоповИван
2СидоровЮрий
3СидоровЮрий
4ПетровВасилий
5ИвановСергей
6ИвановСергей
7ПоповЮрий

Нужно удалить строки, где фамилия и имя совпадают с предыдущей строкой, чтобы таблица стала такой:

ID Family Name
1ПоповИван
4ПетровВасилий
7ПоповЮрий


Записей примерно 50 тысяч, я думаю без оператора While не обойтись. Заранее благодарю.
11 дек 19, 10:42    [22037546]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
court
Member

Откуда:
Сообщений: 2016
genri200,

;with cte as (
	select *, row_number()over(order by ID) as rn from [Есть таблица вида])

delete t1 from cte t1 where exists(select 1 from cte t2 where t1.Family=t2.Family and t1.Name=t2.Name and t1.rn in (t2.rn-1,t2.rn+1))
11 дек 19, 10:50    [22037549]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
genri200
Member

Откуда:
Сообщений: 7
Спасибо огромное, но забыл добавить, что версия базы данных довольно древняя - Microsoft SQL 2008 R2. Инструкция OVER() поддерживается насколько я понимаю от SQL 2012
Можно ли выполнить эту задачу без OVER ?

Сообщение было отредактировано: 11 дек 19, 11:08
11 дек 19, 11:06    [22037565]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
court
Member

Откуда:
Сообщений: 2016
genri200
Инструкция OVER() поддерживается насколько я понимаю от SQL 2012
брэхня ! :)
genri200
Можно ли выполнить эту задачу без OVER

нуу, как-то жили люди и до исторического материализма OVER :)
... не очень, конечно, но для 50к записей должно быть "нестрашно"
;with cte as (
	select *, (select count(*) from [Есть таблица вида] t2 where t2.ID<=t1.ID) as rn from [Есть таблица вида] t1)

delete t1 from cte t1 where exists(select 1 from cte t2 where t1.Family=t2.Family and t1.Name=t2.Name and t1.rn in (t2.rn-1,t2.rn+1))
11 дек 19, 11:16    [22037575]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
DELETE 
FROM mytable
WHERE EXISTS ( SELECT NULL
               FROM mytable t
               WHERE mytable.id     > t.id
                 AND mytable.Family = t.Family
                 AND mytable.Name   = t.Name )


Сообщение было отредактировано: 11 дек 19, 11:22
11 дек 19, 11:20    [22037578]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
genri200
Member

Откуда:
Сообщений: 7
court, Akina - спасибо!
Akina, не совсем понятно из вашего примера, откуда берется таблица t :( Это дубль mytable ?

Сообщение было отредактировано: 11 дек 19, 12:07
11 дек 19, 12:03    [22037624]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
genri200
Akina, не совсем понятно из вашего примера, откуда берется таблица t :( Это дубль mytable ?
Называется "Алиас" (см. справку).
Алиасы позволяют более кратко обзывать таблицы, и использовать в запросе одну таблицу несколько раз.

Я бы и первое упоминание таблицы сделал алиасом. Для понятности:
DELETE m
FROM mytable as m
WHERE EXISTS ( SELECT NULL
               FROM mytable as t
               WHERE m.id     > t.id
                 AND m.Family = t.Family
                 AND m.Name   = t.Name )
11 дек 19, 13:10    [22037711]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
genri200
Member

Откуда:
Сообщений: 7
alexeyvg,

Спасибо, уже разобрался. Один нюанс остается, если будет таблица вида
ID Family Name
1ПоповИван
2СидоровЮрий
3СидоровЮрий
4ПетровВасилий
5ИвановСергей
6ИвановСергей
7ПоповЮрий
8ИвановСергей

То вышеуказанный код как я понимаю удалит всех Ивановых, но 8 строку надо оставить, так как в предыдущей (седьмой) строке не Иванов, а Попов.
11 дек 19, 13:34    [22037733]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
court
Member

Откуда:
Сообщений: 2016
genri200
То вышеуказанный код как я понимаю удалит всех Ивановых, но 8 строку надо оставить, так как в предыдущей (седьмой) строке не Иванов, а Попов.

проверил бы, уже ...

declare @t table (ID int,	Family varchar(50),	Name varchar(50))
insert into @t values
(1,	'Попов',	'Иван'),
(2,	'Сидоров',	'Юрий'),
(3,	'Сидоров',	'Юрий'),
(4,	'Петров',	'Василий'),
(5,	'Иванов',	'Сергей'),
(6,	'Иванов',	'Сергей'),
(7,	'Попов',	'Юрий'),
(8,	'Иванов',	'Сергей')


;with cte as (
	select *, row_number()over(order by ID) as rn from @t)

delete t1 from cte t1 where exists(select 1 from cte t2 where t1.Family=t2.Family and t1.Name=t2.Name and t1.rn in (t2.rn-1,t2.rn+1))

select * from @t 


(8 rows affected)

(4 rows affected)
ID Family Name
----------- -------------------------------------------------- --------------------------------------------------
1 Попов Иван
4 Петров Василий
7 Попов Юрий
8 Иванов Сергей

(4 rows affected)
11 дек 19, 13:40    [22037741]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
invm
Member

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

declare @t table (ID int, Family varchar(30), Name varchar(30));

insert into @t
values
(1, 'Попов', 'Иван'),
(2, 'Сидоров', 'Юрий'),
(3, 'Сидоров', 'Юрий'),
(4, 'Петров', 'Василий'),
(5, 'Иванов', 'Сергей'),
(6, 'Иванов', 'Сергей'),
(7, 'Попов', 'Юрий'),
(8, 'Иванов', 'Сергей');

with a as
(
 select
  row_number() over (order by ID) - row_number() over (partition by Family, Name order by ID) as g
 from
  @t
),
b as
(
 select count(*) over (partition by g) as c from a
)
delete from b where c > 1;

select * from @t;


Сообщение было отредактировано: 11 дек 19, 13:44
11 дек 19, 13:44    [22037744]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
genri200
вышеуказанный код как я понимаю удалит всех Ивановых
Да.
genri200
8 строку надо оставить, так как в предыдущей (седьмой) строке не Иванов, а Попов.
Замените
WHERE m.id > t.id
на
WHERE m.id = t.id + 1

Если же в значении id имеются "дырки" - пронумеруйте записи без дыр в CTE, a потом удаляйте. Или в подзапросе сделайте ещё один коррелированный NOT EXISTS.

Сообщение было отредактировано: 11 дек 19, 14:10
11 дек 19, 14:08    [22037766]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
declare @t table (ID int, Family varchar(30), Name varchar(30));

insert into @t
values
(1, 'Попов', 'Иван'),
(2, 'Сидоров', 'Юрий'),
(3, 'Сидоров', 'Юрий'),
(4, 'Петров', 'Василий'),
(5, 'Иванов', 'Сергей'),
(6, 'Иванов', 'Сергей'),
(7, 'Попов', 'Юрий'),
(8, 'Иванов', 'Сергей');

with a as
(
 select
  ID
  , Family, FamilyLag= LAG(Family, 1, NULL) OVER (ORDER BY ID)
  , Name, NameLag= LAG(Name, 1, NULL) OVER (ORDER BY ID)
 from
  @t
)
DELETE FROM a
WHERE Family = FamilyLag AND Name = NameLag;

select * from @t;
11 дек 19, 15:54    [22037898]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
genri200
Member

Откуда:
Сообщений: 7
Благодарю, пока вариант court и invm сработал корректнее, другой вариант оставляет одну запись из дублей.
court, такой вопрос, что нужно добавить в ваш код, чтобы дополнительно проверить еще на одно условие при удалении, например в таблице

ID Family Name Cond
1ПоповИван1
2СидоровЮрий0
3СидоровЮрий0
4ПетровВасилий1
5ИвановСергей1
6ИвановСергей1
7ПоповЮрий0

удалить строки там, где Cond=1 и не трогать, где Cond=0.

Сообщение было отредактировано: 11 дек 19, 16:34
11 дек 19, 16:30    [22037932]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
genri200
удалить строки там, где Cond=1 и не трогать, где Cond=0.
Добавить "WHERE ... AND Cond=1", вероятно... если имеется в виду поле в удаляемой записи, и пофиг на другие копии.
11 дек 19, 16:56    [22037951]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
genri200
Member

Откуда:
Сообщений: 7
Спасибо всем, в итоге лучше всего подошел код от court с дополнением условия:

with cte as (
	select *, row_number()over(order by ID) as rn from @t)
delete t1 from cte t1 where exists(select 1 from cte t2 where t1.Family=t2.Family and t1.Name=t2.Name and t1.rn in (t2.rn-1,t2.rn+1)) AND Cond=1


Сообщение было отредактировано: 11 дек 19, 18:38
11 дек 19, 18:37    [22038024]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
genri200
Member

Откуда:
Сообщений: 7
Вдогонку еще один вопрос по удалению, есть две таблицы:
ID Family Name
1ПоповИван
3СидоровЮрий
4ПетровВасилий
6ИвановСергей
7ПоповЮрий

и
ID Family Name
2СидоровЮрий
4ПетровВасилий
5ИвановСергей
6ИвановСергей

Нужно удалить строки в обеих таблицах, если хотя бы в одной из таблиц нет ID, которое есть в другой, то есть на выходе должны получиться такие таблицы

ID Family Name
4ПетровВасилий
6ИвановСергей

и
ID Family Name
4ПетровВасилий
6ИвановСергей


ID 4 и 6 присутствуют в обеих таблицах, эти строки не трогаем, остальные удаляем.
11 дек 19, 20:13    [22038104]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
PizzaPizza
Member

Откуда:
Сообщений: 368
genri200

Нужно удалить строки в обеих таблицах, если хотя бы в одной из таблиц нет ID, которое есть в другой


надо ж так сформулировать, что надо оставить только те записи, которые присутствуют в обоих таблицах.
От таких формулировок и запросы соответствующие получаются.
12 дек 19, 07:48    [22038300]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 1492
genri200
совпадают с предыдущей строкой

На будущее. В РСУБД нет такого понятия как предыдущая или следующая строка. Порядок, как правило, нигде не гарантируется. Даже в случае с id надо всё обеспечивать руками, чтобы такая логика не поломалась после очередной "заливки".
12 дек 19, 08:10    [22038307]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строки по результатам сравнения с предыдущей.  [new]
court
Member

Откуда:
Сообщений: 2016
genri200
ID 4 и 6 присутствуют в обеих таблицах, эти строки не трогаем, остальные удаляем.

delete t1
from t1 left join t2 on t1.id=t2.id
where t2.id is null

и второй запрос точно такой же с заменой t1 на t2 и наоборот
12 дек 19, 11:26    [22038493]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить