本文介绍了GROUP_CONCAT 多列作为数组或可分解字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想出的声明:
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 多列作为数组或可分解字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!