Получите лучшие строки на основе нескольких столбцов в MySQL
У меня есть таблица, которая выглядит так:
id | title | value | language
---+-------+-------+---------
1 | a | 1800 | NULL
2 | a | 1900 | NULL
3 | b | 1700 | NULL
4 | b | 1750 | NULL
5 | b | 1790 | 1
6 | c | 1892 | NULL
7 | c | 1900 | 1
8 | c | 1910 | 2
9 | d | 3020 | NULL
Хотелось бы получить следующий результат:
id | title | value | language
---+-------+-------+---------
2 | a | 1900 | NULL
4 | b | 1750 | NULL
5 | b | 1790 | 1
6 | c | 1892 | NULL
7 | c | 1900 | 1
8 | c | 1910 | 2
9 | d | 3020 | NULL
Суть в том, чтобы выбрать наибольшее значение в столбце значений каждого языка каждого заголовка, причем наибольшее значение будет самым последним. Во-вторых, хотелось бы избежать агрегатных функций, таких как MAX, DISTINCT или GROUP-BY, поскольку я создаю представление MySQL с использованием алгоритма MERGE и не хочу в конечном итоге создавать временную таблицу (см. нижний раздел https://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html).
Пока это работает, но возвращает только самую большую строку для заголовка:
SELECT t1.title
FROM table t1
LEFT OUTER JOIN table t2
ON t1.title = t2.title
AND t1.value < t2.value
WHERE t2.title IS NULL
Как я могу создать тот, который учитывает язык, как результаты выше? Спасибо.
@Strawberry Чтобы упростить поиск.
Ответы 3
Этот ответ предполагает, что вы используете MySQL 8+, в котором ваш запрос становится очень простым. MySQL 8 и более поздние версии поддерживают аналитические функции, которые были добавлены с целью решения подобных проблем.
Мы можем попробовать использовать ROW_NUMBER здесь:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY title, language ORDER BY value DESC) rn
FROM yourTable
)
SELECT id, title, value, language
FROM cte
WHERE rn = 1;
Демо
Есть способ справиться с этим с более ранними версиями MySQL, но он требует пользовательских переменных и имеет тенденцию быть очень уродливым. Так что, возможно, подумайте об обновлении, если вы ожидаете много запросов, подобных этому.
Это должно дать вам то, что вы хотите.
SELECT t1.title, t1.value, t1.language
FROM [Table] t1
LEFT OUTER JOIN [Table] t2 ON
t1.title = t2.title AND
(IFNULL(t1.language, '') = IFNULL(t2.language, ''))
WHERE
t1.value > t2.value;
Вы не будете возражать, если я поправлю ваш запрос, чтобы я мог поднять его?
@ДжонМиллер, конечно!
@JohnMiller На самом деле, я считаю, что этот мой ответ вернет повторяющиеся строки, если у вас есть, например, больше записей в заголовке «a» с языком «NULL». Не знаю, подходит ли вам этот сценарий, но имейте в виду.
Вы можете сделать это с помощью NOT EXISTS:
select t.*
from tablename t
where not exists (
select 1 from tablename
where
title = t.title and
coalesce(language, 0) = coalesce(t.language, 0) and
value > t.value
)
Смотрите демо.
Результаты:
| id | title | value | language |
| --- | ----- | ----- | -------- |
| 2 | a | 1900 | NULL |
| 4 | b | 1750 | NULL |
| 5 | b | 1790 | 1 |
| 6 | c | 1892 | NULL |
| 7 | c | 1900 | 1 |
| 8 | c | 1910 | 2 |
| 9 | d | 3020 | NULL |
Еще одна вещь: я предполагаю, что 0 никогда не будет допустимым значением в столбце language. Если это не так, измените 0 на -1 или что-то, чего никогда не было бы в этом столбце.
Спасибо. Поле является внешним ключом к таблице, содержащей «дополнительные» языки. Таким образом, NULL заменяет язык по умолчанию. Но еще раз спасибо за подсказку.
Почему вы строите ВИД?