Sql select查询帮助

发布于 2024-09-16 00:13:40 字数 775 浏览 10 评论 0原文

我有一个包含数据的数据库表，如下所示：

Primary key | Column1       | Column2          | col3 | col4 |col5
---------------------------------------------------------------------

1           | Chicago Bulls | Michael Jordan   | 6'6  | aaaa | cccc

2           | Chicago Bulls | Scottie Pippen   | 6'8  | zzzz | 345

3           | Utah Jazz     | Malone           | 6'9  | vvvv | xcxc

4           | Orlando Magic | Hardaway         | 6'7  | xnnn | sdsd

我想编写一个查询，它将获取 Column1 中的所有不同值，并为每个 Column1 值附加 Column2 中的值。例如：查询应该返回

**Chicago Bulls | Michael Jordan, Scottie Pippen**

**Utah Jazz     | Malone**

**Orlando Magic | Hardaway**

我可以编写一个查询来获取第 1 列中的所有不同值，然后迭代每个不同值以在进行一些操作后获取附加的第 2 列。是否可以仅通过一个查询来完成整个工作？请帮助提供一个查询示例。谢谢。

原文

I have a database table with data as shown below:

Primary key | Column1       | Column2          | col3 | col4 |col5
---------------------------------------------------------------------

1           | Chicago Bulls | Michael Jordan   | 6'6  | aaaa | cccc

2           | Chicago Bulls | Scottie Pippen   | 6'8  | zzzz | 345

3           | Utah Jazz     | Malone           | 6'9  | vvvv | xcxc

4           | Orlando Magic | Hardaway         | 6'7  | xnnn | sdsd

I want to write a query which will fetch all distinct values in Column1 and append values in Column2 for each Column1 value.
For eg: The query should return

**Chicago Bulls | Michael Jordan, Scottie Pippen**

**Utah Jazz     | Malone**

**Orlando Magic | Hardaway**

I can write a query to fetch all distinct values in column1, then iterate over each distinct value to get the appended column 2 after some manipulation. Is it possible to do the entire job in only one query? Please help with an example of a query.
Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

极度宠爱 2024-09-23 00:13:40

如果您使用的是 MySQL

select Column1, group_concat(Column2)
from t
group by Column1

If you are using MySQL

select Column1, group_concat(Column2)
from t
group by Column1

回复收藏 0 原文

最单纯的乌龟 2024-09-23 00:13:40

如果您使用的是 SQL Server：

SELECT Column1,
stuff((
    SELECT ', ' + Column2
    FROM tableName as t1
    where t1.Column1 = t2.Column1
    FOR XML PATH('')
    ), 1, 2, '')
FROM tableName as t2
GROUP BY Column1

不知道为什么 Microsoft 使此操作如此困难，但据我所知，这是在 SQL Server 中执行此操作的唯一方法...

顺便说一句，您可能会考虑将 Column1 更改为查找表否则下次犹他州搬家的时候你就会讨厌生活了;)

If you are using SQL Server:

SELECT Column1,
stuff((
    SELECT ', ' + Column2
    FROM tableName as t1
    where t1.Column1 = t2.Column1
    FOR XML PATH('')
    ), 1, 2, '')
FROM tableName as t2
GROUP BY Column1

Not sure why Microsoft makes this one so hard, but as far as I know this is the only method to do this in SQL Server...

On a side note you might consider changing Column1 to a lookup table or the next time Utah moves you're going to be hating life ;)

回复收藏 0 原文