Удалить строки с повторяющимся значением в столбце, которые имеют определенное значение в другом столбце
У меня есть таблица отчетов (dbo.HighDonorPayments) с данными, как показано ниже:
ID PAYMENTID DONORID MEMBERFIRSTNAME MEMBERLASTNAME ISBIGCHEESE
--------------------------------------------------------------------------
1 4133-ggi 661a Samuel Jackson 1
2 4133-ggi 661a Samuel Jackson 0
3 4133-ggi 661a Samuel Jackson 0
4 9392-ggi 990q Space Eye 0
5 9392-ggi 990q Space Eye 0
Мне нужно удалить повторяющиеся платежи, но очень специфическим способом.
Конечный результат должен быть:
ID PAYMENTID DONORID MEMBERFIRSTNAME MEMBERLASTNAME ISBIGCHEESE
--------------------------------------------------------------------------
1 4133-ggi 661a Samuel Jackson 1
4 9392-ggi 990q Space Eye 0
ИЛИ
ID PAYMENTID DONORID MEMBERFIRSTNAME MEMBERLASTNAME ISBIGCHEESE
--------------------------------------------------------------------------
1 4133-ggi 661a Samuel Jackson 1
5 9392-ggi 990q Space Eye 0
У нас есть таблица «категоризации» для доноров. Пример: Дайте 10 баксов, и вы «хороший», дайте 25, и вы «отличный», дайте 50, и вы «большой сыр». Подвох в том, что (несмотря на мои протесты), если вы дадите нам 10 баксов и попадете в категорию «хорошие», ТОГДА дадите нам 50 позже и станете «БОЛЬШИМ СЫРОМ», у вас будет как «хорошая» запись, так и запись «БОЛЬШОЙ ЧИЗ» в таблице категорий. . Клиент хочет видеть платежи в течение определенного периода времени. Если донор «БОЛЬШОЙ СЫР», он хочет, чтобы эта запись была помечена.
Поскольку я присоединяюсь к таблице «категоризации», я получаю повторяющиеся платежные записи. Мне нужно удалить дубликаты таким образом, чтобы любая запись «ISBIGCHEESE=1» была «защищена», а другие случаи дедуплицировались как обычно.
Итак, в случае Сэмюэля Джексона я хотел бы удалить строки 2 и 3, но защитить строку 1. Но для Space Eye не имеет значения, какая запись удалена.
Я попытался использовать CTE, чтобы избавиться от дубликатов:
WITH eye_cte AS
(
SELECT
paymentid,
ROW_NUMBER() OVER (PARTITION BY paymentid ORDER BY revenueid) row_num
FROM
dbo.HighDonorPayments
)
DELETE FROM eye_cte
WHERE row_num > 1;
Это работает для удаления дубликатов, но запись, которая «сохраняется», является ... случайной, я полагаю. Я не знаю, как сказать: «Эй! Удалите все дубликаты для случая, когда PAYMENTID идентичен, но если у участника рейтинг BIGCHEESE, «защитите» его.
Я понятия не имею, возможно ли то, что я пытаюсь сделать, и мне, возможно, просто придется изменить процесс запроса, который заполняет эту таблицу в первую очередь.
Ответы 3
Я думаю, это должно быть так же просто, как сделать флаг
WITH eye_cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY paymentid ORDER BY revenueid) row_num,
CASE WHEN isbigcheese = 1 THEN 1 ELSE 0 END as is_protected
FROM DBO.highdonorpayments
)
DELETE FROM eye_cte
WHERE row_num > 1 AND is_protected = 0
Если isbigcheese может быть только 1 или 0, MAX(isbigcheese) должен делать то, что вам нужно. Скрипка
SELECT
DISTINCT
MIN(ID) ID
, PAYMENTID
, DONORID
, MEMBERFIRSTNAME
, MEMBERLASTNAME
, MAX(ISBIGCHEESE) ISBIGCHEESE
FROM HighDonorPayments
GROUP BY DONORID, PAYMENTID, MEMBERFIRSTNAME, MEMBERLASTNAME
Возвращает
Мне нужно удалить дубликаты таким образом, чтобы любая запись «ISBIGCHEESE = 1» была «защищена», но другие случаи дедуплицировались, как обычно.
Вы хотите «защитить» эти строки, сгенерировав для них row_num=1, потому что вы DELETE-ing WHERE row_num > 1.
Поэтому вам нужно реорганизовать ORDER BY столбца ROW_NUMBER так, чтобы ISBIGCHEESE=1 строки были первыми.
Чтобы сдвинуть эти ряды вперед, попробуйте ORDER BY CASE WHEN ISBIGCHEESE=1 THEN 0 ELSE 1 END, ... вот так
WITH eye_cte AS
(
SELECT
paymentid,
ROW_NUMBER() OVER (PARTITION BY paymentid ORDER BY
CASE WHEN ISBIGCHEESE=1 THEN 0 ELSE 1 END, revenueid) row_num
FROM
dbo.HighDonorPayments
)
DELETE FROM eye_cte
WHERE row_num > 1;