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.