Possible Duplicate:
Concatenate row values T-SQL可能的重复:连接行值T-SQL。
I have a table like this:
我有一张这样的桌子:
ref_num name type route
----------------------------------------
1 A W 401
2 B X 401
3 C E 401
3 C E 411
4 D Z 401
5 K W 701
5 K W 991
5 K W 556
5 K W 401
6 L X 401
7 D Y 401
7 D Y 411
7 D Y 680
8 E Z 401
And i want a RESULT like this:
我想要一个这样的结果:
ref_num name type routes
----------------------------------------
1 A W 401
2 B X 401
3 C E 401,411
4 D Z 401
5 K W 701,991,556,401
6 L X 401
7 D Y 401,411,680
8 E Z 401
If anybody can give me a query example to get the desired result it would be greatly appreciated
如果有人能给我一个查询示例来得到想要的结果，我将非常感激
3 个解决方案
#1
3
select ref_num, name, type, group_concat(routes)
from your_table
group by ref_num
In MySQL you don't have to group by all values you are selecting. Other values will probably selected by random. But if the other values are the same in one group then you don't have to worry. That is the case in the OP's question.
在MySQL中，您不必对所选择的所有值进行分组。其他的值可能是随机选择的。但是如果一组的其他值是一样的，你就不用担心了。这就是OP的问题。
#2
3
You can Concatenate them with GROUP_CONCAT with GROUP BY, something like:
可以将它们与GROUP_CONCAT和GROUP BY连接起来，如下所示:
SELECT ref_num, name, type, GROUP_CONCAT(routes SEPARATOR ', ')
FROM TableNAme
GROUP BY ref_num, name, type
#3
0
I think WM_CONCAT will do this:
我想WM_CONCAT会这么做:
SELECT REF_NUM, NAME, TYPE, WM_CONCAT(ROUTE)
FROM ROUTES
GROUP BY REF_NUM, NAME, TYPE;
#1
3
select ref_num, name, type, group_concat(routes)
from your_table
group by ref_num
In MySQL you don't have to group by all values you are selecting. Other values will probably selected by random. But if the other values are the same in one group then you don't have to worry. That is the case in the OP's question.
在MySQL中，您不必对所选择的所有值进行分组。其他的值可能是随机选择的。但是如果一组的其他值是一样的，你就不用担心了。这就是OP的问题。
#2
3
You can Concatenate them with GROUP_CONCAT with GROUP BY, something like:
可以将它们与GROUP_CONCAT和GROUP BY连接起来，如下所示:
SELECT ref_num, name, type, GROUP_CONCAT(routes SEPARATOR ', ')
FROM TableNAme
GROUP BY ref_num, name, type
#3
0
I think WM_CONCAT will do this:
我想WM_CONCAT会这么做:
SELECT REF_NUM, NAME, TYPE, WM_CONCAT(ROUTE)
FROM ROUTES
GROUP BY REF_NUM, NAME, TYPE;