ВЫБЕРИТЕ повторяющиеся значения, исключая наиболее часто используемые
У меня есть две соответствующие таблицы в MySQL, которые выглядят примерно так:
marker стол
seat_marker стол
Я хотел бы выбрать все повторяющиеся строки в таблице marker, за исключением той, на которую чаще всего ссылается/соединяется таблица seat_marker. Другими словами, я хотел бы перечислить все дубликаты, за исключением «оригинала», где «оригинал» определяется как строка с наибольшим количеством вхождений в seat_marker.MARKER_ID.
Результат будет выглядеть так:
Мы видим, что это исходные данные из таблицы marker, но с удаленными тремя строками;
- id 1 был удален, потому что это дубликат foo-bar-baz, который встречался чаще в
seat_marker, чем в других строках foo-bar-baz. - идентификатор 4 был удален, поскольку он был дубликатом. Поскольку строки qux-bar-baz появляются 0 раз в
seat_marker, у меня нет предпочтений, удалять ли мы идентификатор 4 или идентификатор 5. Я выбрал идентификатор 4 случайным образом. - идентификатор 6 был удален, поскольку он не является дубликатом.
Самое близкое, что мне удалось получить, это это, в котором перечислены все повторяющиеся значения и количество присвоений. Но не исключает «оригиналов».
-- Select all duplicates, and list the number of times it's referenced in the seat_marker table
SELECT
n1.id, n1.SEASONCD, n1.ITEMCD, n1.PRICETYPECD, (
SELECT COUNT(1)
FROM seat_marker
WHERE MARKER_ID = n1.id
) active_assignments
FROM marker n1, marker n2
WHERE
n1.id <> n2.id AND
n1.SEASONCD = n2.SEASONCD AND
n1.ITEMCD = n2.ITEMCD AND
n1.PRICETYPECD = n2.PRICETYPECD
GROUP BY id, SEASONCD, ITEMCD, PRICETYPECD, active_assignments
ORDER BY SEASONCD, ITEMCD, PRICETYPECD, active_assignments DESC, id;
Пожалуйста, научитесь использовать ANSI JOIN, а не перекрестные произведения.
Я добавил образцы данных и желаемый результат и согласен, что это проясняет вопрос. Извини за это.
Подробно объясните, почему строки со значениями маркера.id 4 и 6 исключаются из желаемого вывода. Например, значение 4 вообще отсутствует в Seat_marker, поэтому его нельзя определить как «наиболее часто упоминаемое».
На самом деле я добавлял это объяснение, когда вы это публиковали!
В таблице результатов вы говорите, что идентификатор 1 удален, потому что он является дубликатом идентификатора 2. Аналогично id: 2 является дубликатом id 3. Итак, как идентификатор 3 отображается в вашей таблице результатов... @uPaymeiFixit Пожалуйста, упростите свой вопрос
Согласно вашим входным данным, это фактические данные вашей таблицы... i.sstatic.net/gYWUWaVI.png где данные строки 1, 2, 3 одинаковы (foo-bar-baz), строки 4, 5 такие же (qux-bar-baz), а строка 6 не дублируется (spam-eggs-ham)
как только маркер 1 будет удален, то 2 и 3 наверняка попадут в правило 2, и один из них должен быть удален случайным образом?
Ответы 1
Ожидаемый результат, включенный в ваш вопрос, кажется неверным. Вместо 4 должно отображаться 5, так как 5 имеет одну соответствующую строку в seat_marker и поэтому является «строкой с наибольшим количеством вхождений».
Один из способов сделать это — с помощью COUNT и ROW_NUMBER():
WITH cte AS (
SELECT m.*,
ROW_NUMBER() OVER (PARTITION BY SEASONCD, ITEMCD, PRICETYPECD ORDER BY COUNT(sm.id) DESC, m.id) AS rn
FROM marker m
LEFT JOIN seat_marker sm ON m.id = sm.MARKER_ID
GROUP BY m.id
)
SELECT * FROM cte WHERE rn > 1;
Выход:
Вот db<>рабочий пример.
Извините, вчера я так долго раздумывал над этим вопросом, что делаю ошибки направо и налево. Я изменил образцы данных и ожидаемые выходные данные, чтобы, надеюсь, все исправить.
Это решение выглядит многообещающе, но, к сожалению, я использую MySQL 5.7, поэтому не могу использовать общие табличные выражения. Есть ли простой способ изменить это, чтобы не использовать WITH?
CTE можно легко заменить производной таблицей, но это вам не поможет, поскольку в MySQL < 8 также отсутствуют оконные функции. Вы могли бы добиться того же с пользовательскими переменными, но обновление — лучший вариант, поскольку версия 5.7 больше не поддерживается.
Поверьте, я бы с удовольствием обновился до 8, если бы мог. Я не могу проверить ваше решение, кроме как на db<>fiddle, но оно кажется правильным, поэтому я отмечу его как решенное.
Вот совместимое с 5.7 решение с использованием пользовательских переменных db<>fiddle
Пожалуйста, предоставьте примерные данные и желаемый результат.