GROUP_CONCAT 多列作为数组或可分解字符串 [英] GROUP_CONCAT multiple columns as an array or explodable string

问题描述

我想出的声明:

   SELECT p.user_id AS started_by,
          p.position,
          p.created AS started_time,
          GROUP_CONCAT(
            c.id,
            c.user_id,
            c.comment,
            c.created) AS comments
     FROM pointers AS p
     JOIN comments AS c
       ON p.id = c.pointer_id
    WHERE p.archive_id = 3
 GROUP BY p.id 

它让我:

发生的事情是 GROUP_CONCAT() 只是连接 c.id .c.user_id c.comment 和 c.created.我什至不能以任何有意义的方式爆炸这个价值.(注意 date 和 id 是如何作为 1 个字符串一起使用的).

What happens is that GROUP_CONCAT() simply concatenates c.id . c.user_id c.comment and c.created. I cannot even explode that value in any meaningful way. (Notice how date and id are together as 1 string).

我不想为评论创建单独的查询.还是我应该?

I don't want to create separate query for comments. OR should I?

无论如何，我需要 orderby c.created 评论，并有两个分隔符.例如:

IN any case, I need orderby c.created the comments, and have two delimiters. for example:

comments => 1,1,text this is!,2014-12-20 17:52:02;3,1,asasd,2014-12-20 20:46:40

推荐答案

如何为列的串联定义一个分隔符，为组本身定义另一个分隔符

How about defining a separator for the concatenation of the columns and another one for the group itself

GROUP_CONCAT(concat_ws(',',
                       c.id,
                       c.user_id,
                       c.comment,
                       c.created)
            separator ';') AS comments

这篇关于GROUP_CONCAT 多列作为数组或可分解字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！