mysql双竖线合并_MySql 将查询结果多列合并成一行GROUP_CONCAT

GROUP_CONCAT(expr)

该函数返回带有来自一个组的连接的非NULL值的字符串结果。其完整的语法如下所示：

GROUP_CONCAT([DISTINCT] expr [,expr ...]

[ORDER BY {unsigned_integer | col_name | expr}

[ASC | DESC] [,col_name ...]]

[SEPARATOR str_val])

CREATE TABLE t (

id VARCHAR(32),

name VARCHAR(50)

);

SELECT * FROM t;

#默认逗号分隔

SELECT id, GROUP_CONCAT(name) FROM t GROUP BY id;

#设置竖线分隔

SELECT id, GROUP_CONCAT(name SEPARATOR '|') FROM t GROUP BY id;

#设置排序

SELECT id, GROUP_CONCAT(name ORDER BY name SEPARATOR '|') FROM t GROUP BY id;

#设置去重

SELECT id, GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR '|') FROM t GROUP BY id;