问题描述

我有 player-s 与 skill-s 多对多关系的表格

I have table with player-s in many-to-many relation with skill-s

目标是通过一个查询列出玩家及其前三项技能".

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

As you can see in the fiddle the result of the query is missing only the limit of 3 skills.
I tried several variation of sub queries.. joins and so but with no effect.

推荐答案

一种有点老套的方法是对 GROUP_CONCAT 的结果进行后处理:

One somewhat hacky way to do it is to post-process the result of GROUP_CONCAT:

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

当然，这假设您的技能名称不包含逗号并且它们的数量相当小.

Of course this assumes that your skill names don't contain commas and that their amount is reasonably small.

小提琴

功能请求，GROUP_CONCAT 支持不幸的是，显式的 LIMIT 子句仍未解决.

A feature request for GROUP_CONCAT to support an explicit LIMIT clause is unfortunately still not resolved.

更新:正如用户 Strawberry 指出的那样，表 player_skills 应该将元组 (player_id, Skill_id) 作为其主键，否则模式允许将同一技能多次分配给玩家，在这种情况下 group_concat 不会按预期工作.

UPDATE: As user Strawberry points out, the table player_skills should have the tuple (player_id, skill_id) as its primary key, otherwise the schema allows for the same skill to be assigned to a player multiple times, in which case group_concat would not work as expected.

这篇关于GROUP_CONCAT 有限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！