MySQL GROUP_CONCAT 有限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23608464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GROUP_CONCAT with limit
提问by d.raev
I have table with player-s in many-to-many relation with skill-s
我有player-s 与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
);
Query:
询问：
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'
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.
正如您在小提琴中看到的，查询结果仅缺少 3 个技能的限制。
我尝试了几种子查询的变体……连接等等，但没有效果。
回答by Niklas B.
One somewhat hacky way to do it is to post-process the result of GROUP_CONCAT:
一种有点hacky的方法是对以下结果进行后处理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.
当然，这假设您的技能名称不包含逗号并且它们的数量相当小。
A feature requestfor GROUP_CONCATto support an explicit LIMITclause is unfortunately still not resolved.
一个功能请求用于GROUP_CONCAT支持一个明确的LIMIT条款是可惜还是没有得到解决。
UPDATE: As user Strawberrypoints out, the table player_skillsshould 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_concatwould not work as expected.
更新：正如用户Strawberry指出的那样，该表player_skills应该将元组(player_id, skill_id)作为其主键，否则该架构允许将同一技能多次分配给玩家，在这种情况下group_concat将无法按预期工作。
回答by Zaib Khan
Increase GROUP_CONCATfunction length using GLOBAL group_concat_max_lenGROUP_CONCAT()maximum length is 1024 characters.
What you can do is to set the GLOBAL group_concat_max_lenin mysql
GROUP_CONCAT使用GLOBAL group_concat_max_lenGROUP_CONCAT()最大长度为 1024 个字符来增加函数长度。
你可以做的是GLOBAL group_concat_max_len在mysql中设置
SET GLOBAL group_concat_max_len = 1000000;
Try this and it will work for sure.
试试这个，它肯定会起作用。
回答by Romain Bruckert
There is a much cleaner solution. Wrap it inside another SELECTstatement.
有一个更清洁的解决方案。将它包装在另一个SELECT语句中。
SELECT GROUP_CONCAT(id) FROM (
SELECT DISTINCT id FROM people LIMIT 4
) AS ids;
/* Result 134756,134754,134751,134750 */
回答by Strawberry
Here's another solution. It includes an arbitrary mechanism for resolving ties, and employes a schema slightly differing from yours...
这是另一个解决方案。它包括一种用于解决关系的任意机制，并采用与您的略有不同的模式......
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
http://sqlfiddle.com/#!2/34497/18
Incidentally, if you have a presentation layer/application-level code, then consider doing all the GROUP_CONCAT stuff there. It's more flexible.
顺便说一句，如果您有一个表示层/应用程序级代码，那么请考虑在那里完成所有 GROUP_CONCAT 的工作。它更灵活。
回答by Lukasz Szozda
It is possible if you are using MariaDB 10.3.3+:
如果您使用的是MariaDB 10.3.3+，则有可能：
Support for LIMIT clause in GROUP_CONCAT()(MDEV-11297)
支持 GROUP_CONCAT() 中的 LIMIT 子句( MDEV-11297)
SELECT p.id,
GROUP_CONCAT(s.title ORDER BY title SEPARATOR ', ' LIMIT 3) 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
GROUP BY p.id
ORDER BY s.id;
回答by Gurwinder Singh
You can simulate the partitioned row_number using user variables and then limit rows and apply group_concat:
您可以使用用户变量模拟分区的 row_number，然后限制行并应用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
演示
This method doesn't require any table to read more than once.
此方法不需要任何表多次读取。
回答by Majbah Habib
Firstly, Set the limit for group concatethen write your query.
首先，设置组连接的限制，然后编写您的查询。
SET SESSION group_concat_max_len = 1200000;
select group_concat(column_name) from table_name group by column_name;
Here, 1200000 means that the query allow 1200000 characters maximum for group concate data.
此处，1200000 表示查询最多允许 1200000 个字符用于组连接数据。
