MySQL 将多行合并为一个空格分隔的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3703740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine multiple rows into one space separated string
提问by haoxu
So I have 5 rows like this
所以我有 5 行这样
userid, col
--------------
1, a
1, b
2, c
2, d
3, e
How would I do query so it will look like this
我将如何进行查询，使其看起来像这样
userid, combined
1, a b
2, c d
3, e
回答by PanchaGil
In hive you can use
在蜂巢中，您可以使用
SELECT userid, collect_set(combined) FROM tabel GROUP BY user_id;
collect_set removes duplicated. If you need to keep them you can check this post:
collect_set 删除重复项。如果您需要保留它们，您可以查看此帖子：
回答by OMG Ponies
Use the GROUP_CONCAT aggregate function:
SELECT yt.userid,
GROUP_CONCAT(yt.col SEPARATOR ' ') AS combined
FROM YOUR_TABLE yt
GROUP BY yt.userid
The default separator is a comma (","), so you need to specify the SEPARATOR of a single space to get the output you desire.
默认分隔符是逗号（“,”），因此您需要指定单个空格的 SEPARATOR 以获得您想要的输出。
If you want to ensure the order of the values in the GROUP_CONCAT, use:
如果要确保 GROUP_CONCAT 中值的顺序，请使用：
SELECT yt.userid,
GROUP_CONCAT(yt.col ORDER BY yt.col SEPARATOR ' ') AS combined
FROM YOUR_TABLE yt
GROUP BY yt.userid
回答by formath
SELECT
userid,
concat_ws(" ", collect_set(col)) AS combined
FROM table
GROUP BY userid
回答by Safwan
MySQLwith duplicates:select col1, group_concat(col2) from table1 group by col1MySQLwithout duplicates:select col1, group_concat(distinct col2) from table1 group by col1Hivewith duplicates:select col1, collect_list(col2) from table1 group by col1Hivewithout duplicates:select col1, collect_set(col2) from table1 group by col1
MySQL有重复：select col1, group_concat(col2) from table1 group by col1MySQL没有重复：select col1, group_concat(distinct col2) from table1 group by col1Hive有重复：select col1, collect_list(col2) from table1 group by col1Hive没有重复：select col1, collect_set(col2) from table1 group by col1
回答by Matthew Hegarty
I'm pretty sure that you can't do this using Hive QL. However, it should be possible to do so if you write your own Map/Reduce scripts - see this tutorialto get started.
我很确定你不能使用 Hive QL 来做到这一点。但是，如果您编写自己的 Map/Reduce 脚本，应该可以这样做 - 请参阅本教程以开始使用。
