Порядок с групповыми рядами вместе
Возникла проблема со сложным заказом.
Вот структура и данные:
CREATE TABLE `mail_test` (
`id` int(10) UNSIGNED NOT NULL,
`account_id` int(10) UNSIGNED NOT NULL,
`score` float UNSIGNED NOT NULL,
`from` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `mail_test` (`id`, `account_id`, `score`, `from`) VALUES
(1, 1, 0, '[email protected]'),
(2, 2, 0, '[email protected]'),
(3, 3, 3, '[email protected]'),
(4, 5, 4, '[email protected]'),
(5, 3, 1, '[email protected]'),
(6, 9, 0.5, '[email protected]'),
(7, 9, 3, '[email protected]'),
(8, 8, 2, '[email protected]');
Желаемый результат:
(4, 5, 4, '[email protected]'),
(7, 9, 3, '[email protected]'),
(3, 3, 3, '[email protected]'),
(5, 3, 1, '[email protected]'),
(8, 8, 2, '[email protected]');
(6, 9, 0.5, '[email protected]'),
(1, 1, 0, '[email protected]'),
(2, 2, 0, '[email protected]'),
Логика порядка: сначала идет строка с наибольшим количеством очков (родительская строка), затем строки (дочерние строки) с одинаковыми account_id и from с родительской строкой. Если нет дочерних строк - снова следующая строка с большим счетом. Итак, сортируйте по счету DESC. Но строки сгруппированы по одинаковым account_id и from.
майскл версии 5.7
Что сложного в порядке сортировки? Что вы пытались (написать сами), чтобы получить желаемый результат?
@Mikael Микаэль, я добавил запрос в ответ. Попробуй это.
Ответы 3
Это можно сделать с помощью row_number() и max() :
with cte as (
select *,
max(score) over (partition by account_id, `from`) as max_score,
row_number() over (partition by account_id, `from` order by score, id) as rn
from mail_test
)
select id, account_id, score, `from`
from cte
order by max_score desc, rn desc
Это рабочее решение для mysql 5.7:
select mt.*
from `mail_test` mt
inner join (
select account_id, `from`, max(score) as max_score
from `mail_test`
group by account_id, `from`
) as s on s.account_id = mt.account_id and s.`from` = mt.`from`
order by max_score desc, id
Результат :
id account_id score from
4 5 4 [email protected]
3 3 3 [email protected]
5 3 1 [email protected]
7 9 3 [email protected]
8 8 2 [email protected]
6 9 0.5 [email protected]
1 1 0 [email protected]
2 2 0 [email protected]
SELECT *
FROM mail_test
ORDER BY MAX(score) OVER (PARTITION BY account_id, `from`) DESC,
account_id, `from`,
ROW_NUMBER() OVER (PARTITION BY account_id, `from` ORDER BY score DESC)
@Mikael Правильно - группы могут иметь одинаковый максимальный балл. Зафиксированный. Группы (3,5) и (7) в моем выводе не соответствуют порядку в желаемом вами выводе, но вы не указали необходимые критерии для сортировки таких групп. Например, это можно исправить с помощью .. ORDER BY .. account_id DESC, ...
возможно ли это на mysql 5.7? извините, я обновил сообщение, версия mysql 5.7
Эмулируйте оконные функции с помощью JOIN + GROUP BY или используйте пользовательские переменные.. но я рекомендую вам обновить версию MySQL.
И как вы думаете, если я перейду с 5.7 на 8 - высоки шансы что-то сломать?
@Mikael 50/50 - либо успех, либо нет. Бэкап (проверен на его восстанавливаемость) поможет.
Эта версия работает без разделов:
SELECT m.`id`, m.`account_id`, m.`score`, m.`from`
FROM mail_test m
LEFT JOIN mail_test parent_m
ON m.account_id = parent_m.account_id
AND m.from = parent_m.from
AND m.score < parent_m.score
GROUP BY m.id, m.account_id, m.score, m.from
ORDER BY IFNULL(MAX(parent_m.score), m.`score`) DESC
Практически идеально ! Но [email protected] с оценкой 3 должен быть выше [email protected] с оценкой 1.
Я немного подправил, теперь должен показывать правильный порядок (хотя и с двусмысленностью относительного порядка id 3 и id 7)
Пожалуйста, добавьте желаемый результат.