Как удалить все строки по группам, кроме первой и последней в SQL Server?
спросил
Изменено 3 года, 5 месяцев назад
Просмотрено 5к раз
У меня есть такие данные
Id Name AuthorId ---------------- 1 ААА 2 2 ВВВ 2 3 ССС 2 4 ДДД 3 5 ЭЭЭ 3
Мне нужен запрос, который удалит все строки по группе AuthorId, если их больше 2, кроме первой и последней.
Например, в приведенных выше данных следует удалить вторую строку, потому что для AuthorId = 2
у меня 3 строки, а для AuthorId = 3
ничего не будет удалено
- sql
- sql -server
- group-by
- sql-delete
Row_number()
дважды и удалить нетерминалы
удалить т от ( выбирать *, row_number() over (раздел по [AuthorId] порядок по [Id]) n1, row_number() over (раздел по [AuthorId] порядок по [Id] desc) n2 из имени таблицы ) т где n1 >1 и n2 > 1
Вы можете попробовать использовать объединение для минимального и максимального идентификатора, а НЕ в результате этого подзапроса
удалить из my_table где id НЕ В ( выберите мин (идентификатор) из my_table сгруппировать по AuthorId союз выберите макс (идентификатор) из my_table сгруппировать по AuthorId )
2
Вы можете попробовать это:
Объявить таблицу @t (id int, name varchar (50), Authorid int) вставить в значения @t (1,'AAA',2) вставить в значения @t (2,'BBB',2) вставить в значения @t (3,'CCC',2) вставить в значения @t (4,'FFF',2) вставить в значения @t (5,'DDD',3) вставить в значения @t (6,'EEE',3) ;с тэ как ( выберите из ( select *,count(*) over (part by authorid) cnt from @t ) т где снт > 2 ) удалить a из cte b присоединиться к @t a на a. id = b.id, где b.id не в (выберите min (id) из группы cte с помощью Authorid) и b.id не в (выберите max (id) из группы cte с помощью автор) выберите * из @t
Попробуйте это,
Объявите таблицу @Temp_Data (id int, name varchar (50), Authorid int) вставить в значения @Temp_Data (1,'AAA',2) вставить в значения @Temp_Data (2,'BBB',2) вставить в значения @Temp_Data (3,'CCC',2) вставить в значения @Temp_Data (4,'DDD',3) вставить в значения @Temp_Data (5,'EEE',3) Удалить из @Temp_Data как внутреннее соединение @Temp_Data как b на a.authorid=b.authorid и b.id > a.id внутреннее соединение @Temp_Data как c на a.authorid=c.authorid и c.id < a.id выберите * из @Temp_Data
С СУЩЕСТВУЕТ
:
удалить t из имени таблицы t где существует ( выберите 1 из имени таблицы где authorid = t.authorid и id > t.id ) и существует ( выберите 1 из имени таблицы где authorid = t.authorid и id < t.id )
См. демонстрацию.
Результаты:
Идентификатор Имя AuthorId 1 ААА 2 3 ССС 2 4 ДДД 3 5 ЭЭЭ 3
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google
Зарегистрироваться через Facebook
Зарегистрируйтесь, используя электронную почту и пароль
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Опубликовать как гость
Электронная почта
Требуется, но не отображается
group by - MySQL: удалить все строки старше 30 дней, но только если их больше одной
спросилИзменено 5 лет, 11 месяцев назад
Просмотрено 8к раз
Мне нужно очистить таблицу, содержащую более 14 000 000 строк. Я хочу удалить все записи старше 30 дней, но только если количество сгруппированных элементов больше одного. Значит, останется хотя бы один предмет.
Получение элементов и их удаление по дате - не такая уж проблема. Но он удалит все.
УДАЛИТЬ ИЗ проанализировано ГДЕ метка времениЯ так и думал
SELECT * ИЗ проанализировано ГДЕ TIMESTAMP < UNIX_TIMESTAMP (DATE_SUB (СЕЙЧАС (), ИНТЕРВАЛ 8 ДНЕЙ)) И ( ( ВЫБЕРИТЕ КОЛИЧЕСТВО(*) ИЗ проанализировано СГРУППИРОВАТЬ ПО item_id ) >1 ) Но это не сработает. Спасибо за помощь!
- mysql
- group-by
- delete
Любая попытка выполнить это удаление в одном запросе может занять часы, а то и дни. Если это нормально, то код @ypercube, вероятно, достаточно хорош.
В противном случае я бы рекомендовал заниматься поэтапно.
Сначала найдите элементы, которые нужно удалить. Это будет относительно быстро и неинвазивно.
СОЗДАТЬ ВРЕМЕННУЮ ТАБЛИЦУ t (ПЕРВИЧНЫЙ КЛЮЧ (идентификатор_элемента)) ВЫБЕРИТЕ item_id, MAX (отметка времени) AS keeper ИЗ проанализировано ГДЕ метка времени <... СГРУППИРОВАТЬ ПО item_id СЧЕТ(*) > 1;Мы будем использовать
PRIMARY KEY
для эффективного обходаt
.keeper
- это строка , а не , которую нужно удалить.Теперь пройдемся по
t
кусками, удалив изпроанализированных
. Но сначала давайте посмотрим наDELETE
:DELETE проанализировано ИЗ проанализировано Соединение ГДЕ parsed.item_id = t.item_id И parsed.timestamp != t.keeper;(Пожалуйста, убедитесь, что я правильно понял синтаксис работы с несколькими таблицами.)
Затем введите цикл и добавьте
И
. В псевдокоде:SELECT @a := MIN(item_id) from t; петля: SELECT @z := item_id FROM t ORDER BY item_id LIMIT 100, 1; УДАЛИТЬ . .. И t.item_id МЕЖДУ @a и @z; ВЫБЕРИТЕ @a := @z; вернуться к циклу(Все еще есть конечный случай, с которым нужно справиться — вам нужно выйти из цикла, когда в последнем фрагменте меньше 100 элементов, и выполнить несколько последних элементов.)
Подробнее обсуждение . Я думаю, что лучший способ — скопировать те данные, которые вы хотите сохранить, в другую таблицу, затем удалить их, а затем скопировать обратно.
Основная проблема заключается в том, что непросто НЕ удалить ОДНУ из повторяющихся строк. Сохраняя их в отдельной временной таблице, вы избегаете проблемы их удаления.
У меня нет MySQL на моем ПК, поэтому вам может понадобиться немного настроить, но вот как я решил бы это с помощью MS SQL Server.
-- Создать демонстрационную таблицу DECLARE @MyTable TABLE ( IdField INT, DateField DATE, GroupField CHAR (1)) ВСТАВИТЬ В @MyTable ([IdField], [DateField], [GroupField]) ВЫБЕРИТЕ 1, '2015-03-01', 'A' ОБЪЕДИНЕНИЕ SELECT 2, '2015-04-01', 'A' UNION SELECT 3, '2015-05-01', 'A' UNION SELECT 4, '2015-03-01', 'B' UNION SELECT 5, '2015-04-01', 'C' UNION SELECT 6, '2015-05-01', 'C' ВЫБЕРИТЕ * ИЗ @MyTable -- Получите более 30 дней и подсчитайте > 1 SELECT * FROM @MyTable WHERE [DateField] < '2015-04-13' И [GroupField] IN ( ВЫБЕРИТЕ [GroupField] ИЗ @MyTable GROUP BY [GroupField] HAVING COUNT (*) > 1 )Один из способов написать оператор
DELETE
:DELETE FROM проанализировано AS p ГДЕ p.