MySql 将查询结果多列合并成一行GROUP_CONCAT

小鱼儿 2024-04-20 08:56 3阅读 0赞

**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;

![format_png][]

#默认逗号分隔
    SELECT id, GROUP_CONCAT(name) FROM t GROUP BY id;

![format_png 1][]

#设置竖线分隔
    SELECT id, GROUP_CONCAT(name SEPARATOR '|') FROM t GROUP BY id;

![format_png 2][]

#设置排序
    SELECT id, GROUP_CONCAT(name ORDER BY name SEPARATOR '|') FROM t GROUP BY id;

![format_png 3][]

#设置去重
    SELECT id, GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR '|') FROM t GROUP BY id;

![format_png 4][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyMy84NDkzL2U2OTllNzc3LWIyMTUtMzdlMC05OTNhLWNhMTgwODBlZjRlYi5wbmc?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyMy84NDk1L2Y1YWIxMTAzLWQwODctMzg1Ni1hMzFiLWY3NDkyMTEyNTNiNC5wbmc?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyMy84NDk3L2EwODdkYmI2LWI4OGMtMzhhZi1iMGU3LWYzMmNjOTRkNWJiYi5wbmc?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyMy84NDk5LzIxOTUwNjhhLWU4YTAtMzc1YS05MDE4LThhNzdkNzViMDM3YS5wbmc?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyMy84NTAxLzJjYjc3MTQzLWNmMDAtMzA2Zi04MjRjLWM2MTIwYjM4MWY1Ny5wbmc?x-oss-process=image/format,png