GROUP_CONCAT con límite

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

El objective es hacer una lista de los jugadores y sus “3 habilidades principales” con una sola consulta.

violín

 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 solo le falta el límite de 3 habilidades.
Intenté varias variaciones de subconsultas … se une y así, pero sin efecto.

Una forma un tanto hacky de hacerlo es post-procesar el resultado de GROUP_CONCAT :

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

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

violín

Una solicitud de función para GROUP_CONCAT para soportar una cláusula LIMIT explícita desafortunadamente aún no se resuelve.

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

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

 SET GLOBAL group_concat_max_len = 1000000; 

Prueba esto y funcionará con seguridad.

Aquí hay otra solución. Incluye un mecanismo arbitrario para resolver los 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

Por cierto, si tiene un código de capa de presentación / nivel de aplicación, entonces considere hacer todo el material de GROUP_CONCAT allí. Es mas flexible

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 */ 

Puede simular el row_number particionado utilizando variables de usuario y luego limitar las 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; 

Manifestación

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