comando - mysql separator group_concat

GROUP_CONCAT con límite

Question

Tengo mesa con player -s en relación de muchos a muchos con skill -s

El objetivo es enumerar a los jugadores y sus "habilidades principales 3" con una sola consulta.

fiddle

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
);

Consulta:

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''

Como puede ver en el violín, el resultado de la consulta le falta solo el límite de 3 habilidades.
Probé varias variaciones de subconsultas ... uniones y así, pero sin efecto.

Answer 1

Aquí hay otra solución. Incluye un mecanismo arbitrario para resolver vínculos y emplea un esquema ligeramente diferente al suyo ...

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

Incidentalmente, si tienes un código de nivel de aplicación / capa de presentación, entonces considera hacer todas las cosas de GROUP_CONCAT allí. Es más flexible.

Answer 2

Aumente la longitud de la función GROUP_CONCAT utilizando GLOBAL group_concat_max_len GROUP_CONCAT() la longitud máxima es de 1024 caracteres.
Lo que puedes hacer es configurar el GLOBAL group_concat_max_len en mysql

SET GLOBAL group_concat_max_len = 1000000;

Intenta esto y funcionará seguro.

Answer 3

Hay una solución mucho más limpia. Envuélvalo dentro de otra instrucción SELECT .

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

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

Answer 4

Puede simular el número de fila particionado usando variables de usuario y luego limitar filas y aplicar 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;

Demo

Este método no requiere ninguna tabla para leer más de una vez.

Answer 5

Una forma un tanto intrincada de hacerlo es postprocesar el resultado de GROUP_CONCAT :

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

Por supuesto, esto supone que los nombres de tus habilidades no contienen comas y que su cantidad es razonablemente pequeña.

fiddle

Lamentablemente, aún no se ha resuelto una solicitud de función para que GROUP_CONCAT admita una cláusula LIMIT explícita.

ACTUALIZACIÓN : como señala el usuario , la tabla player_skills debe tener la tupla (player_id, skill_id) como su clave principal, de lo contrario, el esquema permite que la misma habilidad se asigne a un jugador varias veces, en cuyo caso group_concat no funcionaría como esperado.