GROUP BY 但从其他列中获取所有值 [英] GROUP BY but get all values from other column

问题描述

我将举例说明我需要做什么.首先，我们有一个像这样的简单表，名为table:

I''ll explain what I need to do on example. First of all, we have a simple table like this one, named table:

id | name
===+=====
1  | foo
1  | bar
1  | foobar
2  | foo
2  | bar
2  | foobar

现在查询:

SELECT t.* FROM table t GROUP BY t.id

会得到与此类似的结果:

Will get us result similar to this one:

id | name
===+=====
1  | foo
2  | foo

但是是否有可能收集 name 的所有值以获得这样的结果?

But is it possible, to collect all values of name to have result like this?

id | name
===+=================
1  | foo, bar, foobar
2  | foo, bar, foobar

推荐答案

使用 MySQL 你可以使用 GROUP_CONCAT(expr)

Using MySQL you can use GROUP_CONCAT(expr)

此函数返回一个字符串结果，其中连接了非 NULL来自一组的值.如果没有非 NULL 值，则返回 NULL.完整语法如下:

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

类似的东西

SELECT ID, GROUP_CONCAT(name) GroupedName
FROM Table1
GROUP BY ID

这篇关于GROUP BY 但从其他列中获取所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！