GROUP_CONCAT 有限制

The goal is to list the players and their "top 3 skills" with a single query.

小提琴

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

查询:

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 个技能的限制.
我尝试了几种子查询的变体.. 加入等等，但没有效果.

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