GROUP BY,但获取其他列的所有值 [英] GROUP BY but get all values from other column
问题描述
id |名称
=== + =====
1 | foo
1 | bar
1 | foobar
2 | foo
2 | bar
2 | foobar
现在查询:
SELECT t。* FROM table t GROUP BY t.id
让我们得到与此相似的结果:
id |名称
=== + =====
1 | foo
2 | foo
但有可能收集 name 的所有值结果是这样吗?
id |名称
=== + =================
1 | foo,bar,foobar
2 | foo,bar,foobar
a href =http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat =noreferrer> GROUP_CONCAT(expr) p>
该函数返回一个字符串结果,其中包含来自组的串联的非空
值。如果没有非NULL值,它将返回NULL。
完整的语法如下:
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
SQL小提琴演示
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
Now the query:
SELECT t.* FROM table t GROUP BY t.id
Will get us result similar to this one:
id | name
===+=====
1 | foo
2 | foo
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
Using MySQL you can use GROUP_CONCAT(expr)
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])
Something like
SELECT ID, GROUP_CONCAT(name) GroupedName
FROM Table1
GROUP BY ID
SQL Fiddle DEMO
这篇关于GROUP BY,但获取其他列的所有值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!