Лимит mysql в группе на
Скажем, у меня есть поля этой таблицы:
Field1 Field2 Field3
a 1 1
a 1 2
a 1 3
a 1 4
a 2 1
a 2 2
a 2 3
a 3 1
b 1 1
b 1 2
b 1 3
b 2 1
c 1 1
и скажите, что мне нужно сгруппировать по Field1 (потому что мне нужно некоторое вычисление данных), есть ли способ получить только первые 2 элемента, сгруппированные по Field2 (отсортировано по убыванию)?
Итак, для этого примера я хотел бы получить:
a,3,1
a,2,3
a,2,2
a,1,4
a,1,3
b,1,2
b,1,1
b,2,1
c,1,1
было бы что-то вроде
SELECT field1,
Sum(field2),
Sum(field3)
FROM table t1
WHERE t1.Field1 IN (SELECT t2.Field1
FROM table t2
WHERE t2.Field1 = t1.Field1
ORDER BY Field2,
Field3 DESC
LIMIT 2)
GROUP BY field1
Возможный дубликат Использование LIMIT в GROUP BY для получения N результатов на группу?
Я на версии 5.7.18
Вы уверены, что хотите Sum(field2)? Значение повторяется.
@renemarxis Тогда проверьте ссылку, указанную в комментарии непосредственно над вашим комментарием. Или обновитесь до MySQL 8+ и используйте мой ответ.
Ответы 2
Хороший способ справиться с этим, если вы используете MySQL 8+ или более позднюю версию, — использовать ROW_NUMBER:
WITH cte AS (
SELECT Field1, Field2, Field3,
ROW_NUMBER() OVER (PARTITION BY Field1, Field2 ORDER BY Field3 DESC) rn
FROM yourTable
)
SELECT Field1, Field2, Field3
FROM cte
WHERE rn <= 2
ORDER BY Field1, Field2 DESC, Field3;
Демо
Если вы нет используете MySQL 8+, то эта проблема становится довольно сложной и довольно быстро. Лучший способ сделать это в MySQL 5.7 или более ранней версии — использовать переменные сеанса для имитации ROW_NUMBER. Но для этого требуется некоторый подробный код. Вы также можете попытаться изолировать две верхние записи в каждой группе, но эти две записи потребуют многословного кода. Действительно, если вам нужно выполнять подобные запросы в долгосрочной перспективе, подумайте об обновлении до MySQL 8+.
FWIW, я думаю, что запрос 5.7 имеет ту же длину символов, что и запрос 8.0.
Предварительное решение MySQL 8.0:
Укажите только два последних значения в группе (Field1, Field2):
select *
from test t
where t.Field3 >= coalesce((
select t1.Field3
from test t1
where t1.Field1 = t.Field1
and t1.Field2 = t.Field2
order by t1.Field3 desc
limit 1
offset 1
), 0)
order by Field1, Field2, Field3;
Результат:
Field1 Field2 Field3
a 1 3
a 1 4
a 2 2
a 2 3
a 3 1
b 1 2
b 1 3
b 2 1
c 1 1
Получите SUM за группу Field1:
select t.Field1
, sum(Field2)
, sum(Field3)
from test t
where t.Field3 >= coalesce((
select t1.Field3
from test t1
where t1.Field1 = t.Field1
and t1.Field2 = t.Field2
order by t1.Field3 desc
limit 1
offset 1
), 0)
group by t.Field1
Результат:
Field1 sum(Field2) sum(Field3)
a 9 13
b 4 6
c 1 1
Примечание: это прекрасно работает, когда комбинация (Filed1, Filed2, Filed3) УНИКАЛЬНА. Если Field3 можно продублировать в группе (Field1, Field2), нам потребуется более сложная логика, чтобы ограничить результат двумя строками на группу.
Какую версию MySql вы используете?