#sql-server #duplicates
Вопрос:
— Есть какие-нибудь предложения, как заставить это работать в MS SQL Server 2008?
— Мое определение «повторяющейся строки» — «совпадают первые 3 поля» — мне нужно исключить дубликаты из моих результатов, но я решаю, какую 1 строку сохранить-Сохраните строки, имеющие значение в 4-м столбце (для каждого набора повторяющихся пар будет только 1 строка).
— Результаты сохранят строку 2 (но не строку 1) — Также сохранят строку 3 (но не строку 4) — Также сохранят все оставшиеся строки (ни одна из них не дублируется) — 8 из 10 строк должны быть возвращены
IF(OBJECT_ID('tempdb..#tmp') IS NOT NULL) DROP TABLE #tmp CREATE TABLE #tmp ( aKey Int IDENTITY(1,1) PRIMARY KEY, f1 VarChar(10) NOT NULL DEFAULT 0, -- 1 f2 VarChar(10) NOT NULL DEFAULT 0, -- 2 f3 VarChar(10) NOT NULL DEFAULT 0, -- 3 f4 VarChar(10) NOT NULL DEFAULT 0, f5 VarChar(10) NOT NULL DEFAULT 0, f6 VarChar(10) NOT NULL DEFAULT 0 ) INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('A', 'B', 'C', '' , 'del', '1') -- 1st of the duplicate INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('A', 'B', 'C', 'D', 'keep', '1') -- 2nd of the duplicate INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('D', 'E', 'F', 'G', 'keep', '2') -- 1st of the duplicate INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('D', 'E', 'F', '' , 'del', '2') -- 2nd of the duplicate INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('H', 'G', 'N', 'Q', '1', 'K') INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('I', 'G', 'C', '' , '2', 'L') INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('J', 'H', 'D', 'R', '3', 'P') INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('K', 'G', 'C', '' , '4', 'K') INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('L', 'G', 'C', 'S', '5', 'V') INSERT INTO #tmp(f1, f2, f3, f4, f5, f6) VALUES('K', 'M', 'C', '' , '6', 'K') —ВЫБЕРИТЕ * ИЗ #tmp
-- This "almost" works, but is excluding too many non-duplicate rows: SELECT DISTINCT t1.* FROM #tmp AS t1 INNER JOIN #tmp AS t2 ON t1.f1 = t2.f1 AND t1.f2 = t2.f2 AND t1.f3 = t2.f3 WHERE t1.f4 lt;gt; '' Комментарии:
1. и что произойдет, если у вас есть две записи со значениями в столбце 4, но 3 столбца перед совпадением? по сути, вы хотите использовать
row_number() over (partition by f1,f2,f3, f4 desc)и возвращать только строку 1.Это присваивает номер строки дубликату ech «Нравится», а затем возвращает только 1-ю строку. но, учитывая ваше условие возвращать только те, у которых значение в f4… слишком много неясности в определении того, какую запись использовать.
Ответ №1:
Прежде всего, отличная работа по размещению образцов данных в формате, который могут использовать люди. Браво!!!! Это облегчает людям помощь. Во-вторых, даже несмотря на то, что ваше изложенное объяснение было не совсем ясным, вы предоставили достаточно подробных данных в образцах, чтобы было ясно, чего вам нужно достичь. xQbert заметил, что использование ROW_NUMBER-отличный способ решить эту проблему.
Я использовал cte здесь, чтобы внести некоторую ясность в то, как это работает. Вы можете сделать это в качестве подзапроса так же легко, если захотите.
with SortedValues as ( select * , RowNum = ROW_NUMBER() over (partition by f1, f2, f3 order by f4 desc) from #tmp ) select * from SortedValues where RowNum = 1 order by aKey Комментарии:
1. Возможно, лучше всего также объяснить, что пустые строки
''иNULLсортируются до всего остального (или после, если сортировкаDESCтакая, какая есть здесь)2. Отличная мысль @Charlieface.
3. Я обнаружил, что «вложенный метод» легче понять. Но когда я попробовал метод «с/как/над/разделом» (1-й раз, когда я когда-либо работал с ним)… это сработало так же хорошо. Браво! И бонус, теперь я могу использовать «с/как/над/разделом» в будущих проектах, так как это действительно не так сложно понять.
Ответ №2:
В соответствии с вашим существующим подходом, вместо присоединения просто используйте exists
select * from t where not exists ( select * from t t2 where t.f1=t2.f1 and t.f2=t2.f2 and t.f3=t2.f3 and t.f4 ='' and t2.akey !=t.akey ) Комментарии:
1. Это вряд ли будет так же эффективно, как подход с нумерацией строк
2. Это приведет к более логичному чтению таблицы, номер строки потребует сортировки строк; Тестирование на реальных данных, очевидно, покажет, какой из них лучше
3. Не обязательно так:
existsв любом случае соединение может быть преобразовано в соединение типа «сортировка-слияние», и в конечномorder byзапросе @SeanLange на самом деле нет необходимости. Я признаю, что ваше решение, вероятно, быстрее, если очень значительный процент строк дублируется, но это необычно. Согласовано, что нужно протестировать данные в реальном мире4. @Charlieface на самом деле без окончательного порядка по не было бы никакого способа определить порядок строк в выводе. Если заказ не имеет значения, то да, вы правы.