有限制的GROUP_CONCAT [英] GROUP_CONCAT with limit
问题描述
我有一个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.
更新:由于用户草莓指出,表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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!