使用带有限制的GROUP_CONCAT函数

Question

我有一张表格，其中player和skill之间存在多对多的关系。

目标是使用单个查询列出玩家及其“前三项技能”。

示例

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'

正如您在fiddle中所看到的，查询结果仅缺少3个技能的限制。
我尝试了多种子查询、连接等变体，但都没有效果。

- d.raev

---

也许你可以在这里找到解决方案 https://dev59.com/sUnSa4cB1Zd3GeqPP6hN - Imran Qamer

Answer 1

有一个有点巧妙的方法是对 GROUP_CONCAT 的结果进行后处理：

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

当然，这假设你的技能名称不包含逗号，并且它们的数量是相对较少的。

演示

对于 GROUP_CONCAT 支持显式的 LIMIT 子句的 功能请求，遗憾的是仍未得到解决。

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

- Niklas B.

---

比起多重嵌套查询，这种方法更好，因为技能本来就是有限的。 - d.raev

由于奇怪（可能是冗余的）PK，这应该是GROUP_CONCAT（DISTINCT... - Strawberry

@Strawberry 谢谢，我在我的答案中添加了关于此事的评论。添加适当的约束可能是解决问题的方法。 - Niklas B.

我本来期望有更加规范的方式，但这个 hack 更加简洁和清晰，对于我的情况来说更好，比起多层嵌套查询。感谢你超越常规的思考。 - d.raev

很遗憾，根据快速测试结果，不行。如果在事先没有适当调整group_concat_max_len的情况下，长结果将生成警告“第270行被GROUP_CONCAT()截断”。 - Martin Hennings

Answer 2

通过使用GLOBAL group_concat_max_len增加GROUP_CONCAT函数的长度。

GROUP_CONCAT()最大长度为1024个字符。
你可以在mysql中设置GLOBAL group_concat_max_len

SET GLOBAL group_concat_max_len = 1000000;

试试这个，肯定会起作用。

- Zaib Khan

Answer 3

有一个更简洁的解决方案。将它包裹在另一个SELECT语句中。

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

/* Result 134756,134754,134751,134750 */

- Romain Bruckert

---

MySQL不能处理子查询中的LIMIT语句，这已经让我烦恼了相当长一段时间 :-/ - Theodore R. Smith

这个解决方案对我使用ezSQL的mysqli版本有效，谢谢。 - carnini

Answer 4

如果你正在使用MariaDB 10.3.3+，那么在GROUP_CONCAT()中支持LIMIT子句。

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;

db<>fiddle演示

- Lukasz Szozda

---

SELECT GROUP_CONCAT(questionbankID ORDER BY RAND() SEPARATOR ', ' LIMIT 3) AS questionbankID FROM questionbank WHERE clientID = 82 AND lessonID = 184 AND questionType = 'objective' AND type = 'A'非常相似的查询给我带来了错误。 - Alok Jha

Answer 5

这里有另一种解决方案。它包括一个用于解决平局的任意机制，并采用与您略有不同的模式...

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

顺便提一下，如果您有展示层/应用级别的代码，那么请考虑在那里完成所有GROUP_CONCAT操作。这样更加灵活。

- Strawberry

Answer 6

你可以按照以下指示解决这种问题。

指示1：设置组合限制，然后编写查询语句。

SET SESSION group_concat_max_len = 1200000;

步骤 2：接下来，您可以根据以下两个示例找到解决方案。

示例 1：

SELECT GROUP_CONCAT(app_id) AS ids FROM (
      SELECT DISTINCT app_id FROM email_queue 
) AS ids;

例子2：

select GROUP_CONCAT(caption)  from email_queue group BY process_type_id;

注意 1：这是查询example1和example2的表结构。

.

注意 2：在这里，1200000表示查询允许最多使用1200000个字符来进行分组连接数据。

- Majbah Habib

---

你能解释一下为什么要设置限制吗？这有什么必要吗？被接受的答案没有包含这个信息。 - Nico Haase

当GROUP_CONCAT相关列包含超过1024个字符时，您需要使用此方法来获取GROUP_CONCAT函数的完整结果。 更多详情请参见：https://www.namasteui.com/mysql-group_concat-maximum-length/ - Majbah Habib

请将所有这样的解释添加到答案本身，而不是评论部分。 - Nico Haase

Answer 7

您可以使用用户变量模拟分区的行号，然后限制行数并应用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;

演示

这种方法不需要读取任何表超过一次。

- Gurwinder Singh