GROUP_CONCAT с лимитом

У меня есть таблица с player -s в отношении многих ко многим со skill -s

Цель состоит в том, чтобы перечислить игроков и их "топ-3 навыков" с помощью одного запроса.

create table player(
  id int primary key
);

create table skill(
  id int primary key,
  title varchar(100)
);

create table player_skills (
  id int primary key,
  player_id int,
  skill_id int,
  value int
);

Query:

SELECT 
p.id,  
group_concat(s.title  SEPARATOR ', ') as skills

FROM player p
LEFT JOIN player_skills ps ON ps.player_id = p.id
LEFT JOIN skill s ON s.id = ps.skill_id

WHERE ps.value > 2
-- skills limit 3 some how ...
group by p.id 
order by s.id


-- expected result
-- player_ID, skills
-- 1 , 'one'
-- 2 , 'one'
-- 3 , 'two, three, four'

Как вы можете видеть в скрипте, в результате запроса не хватает только 3-х навыков.
Я попробовал несколько вариантов подзапросов.. присоединяется и так, но без эффекта.

Ответ 1

Один довольно хакерский способ сделать это - обработать результат GROUP_CONCAT:

substring_index(group_concat(s.title SEPARATOR ','), ',', 3) as skills

Конечно, это предполагает, что ваши имена навыков не содержат запятых и что их количество достаточно мало.

fiddle

A запрос функции для GROUP_CONCAT для поддержки явного предложения LIMIT, к сожалению, еще не разрешен.

UPDATE. Как указывает пользователь Strawberry, таблица player_skills должна иметь кортеж (player_id, skill_id) в качестве основного ключа, в противном случае схема позволяет одному и тому же навыку назначаться игроку несколько раз, и в этом случае GROUP_CONCAT не будет работать, как ожидалось.

Ответ 2

Увеличьте функциональную длину GROUP_CONCAT, используя GLOBAL group_concat_max_len GROUP_CONCAT() максимальная длина - 1024 символа.
Что вы можете сделать, так это установить GLOBAL group_concat_max_len в mysql

SET GLOBAL group_concat_max_len = 1000000;

Попробуйте это, и это сработает наверняка.

Ответ 3

Существует гораздо более чистое решение. Оберните его в другой оператор SELECT.

SELECT GROUP_CONCAT(id) FROM (
    SELECT DISTINCT id FROM people LIMIT 4
) AS ids;

/* Result 134756,134754,134751,134750 */

Ответ 4

Здесь другое решение. Он включает в себя произвольный механизм разрешения связей и использует схему, немного отличающуюся от вашей...

SELECT a.player_id
     , GROUP_CONCAT(s.title ORDER BY rank) skills
  FROM
     ( SELECT x.*, COUNT(*) rank
         FROM player_skills x
         JOIN player_skills y 
           ON y.player_id = x.player_id
          AND (y.value > x.value
           OR (y.value = x.value AND y.skill_id <= x.skill_id))
        GROUP 
           BY player_id, value, skill_id
       HAVING COUNT(*) <= 3
     ) a
  JOIN skill s
    ON s.skill_id = a.skill_id
 GROUP 
    BY player_id;

http://sqlfiddle.com/#!2/34497/18

Кстати, если у вас есть уровень уровня представления/уровня приложения, тогда подумайте о том, чтобы делать все вещи GROUP_CONCAT. Это более гибко.

Ответ 5

Вы можете моделировать секционированный row_number с использованием пользовательских переменных, а затем ограничивать строки и применять group_concat:

select p.id,
    group_concat(s.title separator ', ') as skills
from player p
left join (
    select distinct ps.player_id,
        ps.skill_id,
        @rn := if(@player_id = player_id, @rn+1, if(@player_id := player_id, 1, 1)) as seqnum
    from player_skills ps
    cross join (select @rn := 0, @player_id := null) x
    where ps.value > 2
    order by player_id, value desc
    ) ps on p.id = ps.player_id and ps.seqnum <= 3
left join skill s on ps.skill_id = s.id
group by p.id;

Демо

Этот метод не требует, чтобы какая-либо таблица читалась более одного раза.

Ответ 6

Это возможно, если вы используете MariaDB 10.3. 3+:

Поддержка предложения LIMIT в GROUP_CONCAT() (MDEV-11297)

SELECT p.id,  
   GROUP_CONCAT(s.title ORDER BY title  SEPARATOR ', ' LIMIT 3) as skills
FROM player p
LEFT JOIN player_skills ps ON ps.player_id = p.id
LEFT JOIN skill s ON s.id = ps.skill_id
WHERE ps.value > 2
GROUP BY p.id 
ORDER BY s.id;

db <> Fiddle demo