SQL查询连接一个列并有其他列?(复制)

时间:2021-09-27 00:36:27

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;