Konkatedra grupowa z limitem

Mam tabelę z player - S w relacji many-to-many z skill - S

Celem jest lista graczy i ich "top 3 umiejętności" za pomocą jednego zapytania.

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

Zapytanie: 

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'

Jak widać w fiddle w wyniku zapytania brakuje tylko limitu 3 umiejętności.
Próbowałem kilku wariacji zapytań podrzędnych.. łączy i tak, ale bez efektu.

Author: Strawberry, 2014-05-12
Source

5 answers

Jednym z nieco hakerskich sposobów na to jest przetworzenie wyniku GROUP_CONCAT: 

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

Oczywiście zakłada to, że Twoje nazwy umiejętności nie zawierają przecinków i że ich ilość jest dość mała.

Fiddle

A feature request for GROUP_CONCAT to support a explicit LIMIT clause is niestety still not resolved.

UPDATE: jak zauważa użytkownik Strawberry, Tabela player_skills powinna mieć krotkę (player_id, skill_id) jako klucz podstawowy, w przeciwnym razie schemat pozwala na wielokrotne przypisanie tej samej umiejętności graczowi, w którym to przypadku group_concat nie będzie działać zgodnie z oczekiwaniami.

 67
Author: Niklas B.,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:26:09

Zwiększ GROUP_CONCAT Długość funkcji za pomocą GLOBAL group_concat_max_len GROUP_CONCAT() Maksymalna długość to 1024 znaków.
Możesz ustawić GLOBAL group_concat_max_len w mysql 

SET GLOBAL group_concat_max_len = 1000000;
Spróbuj tego, a na pewno zadziała.
 11
Author: Zaib Khan,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-25 15:45:05

Oto inne rozwiązanie. Zawiera arbitralny mechanizm rozwiązywania więzi i wykorzystuje schemat nieco różniący się od Twojego...

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

Nawiasem mówiąc, jeśli masz kod warstwy prezentacji/poziomu aplikacji, rozważ zrobienie tam wszystkich rzeczy GROUP_CONCAT. Jest bardziej elastyczny.

 5
Author: Strawberry,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-05-12 12:51:10

Jest dużo czystsze rozwiązanie. Owinąć w inne SELECT stwierdzenie.

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

/* Result 134756,134754,134751,134750 */
 4
Author: Romain Bruckert,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-23 14:22:42

Możesz zasymulować partycjonowany numer wiersza za pomocą zmiennych użytkownika, a następnie ograniczyć wiersze i zastosować 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

Ta metoda nie wymaga odczytu tabeli więcej niż jeden raz.

 0
Author: Gurwinder Singh,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-13 08:30:24