MS SQL在一组行上的字符串连接 [英] String concat on MS SQL for a group of rows

问题描述

让我们说我有2张桌子:

Lets say I have 2 tables:

1位用户 另一个记录哪些用户使用了什么代码.

1 with users and another one which keeps record of which users used what codes.

Users
-----
Id, Name
1, 'John'
2, 'Doe'

Codes
------
Id, UserId, code
1, 1, 145
2, 1, 187
3, 2, 251

现在我想查询一个导致他关注的查询

Now I want to pull a query that results he following

Name, UsedCodes
'John', '145,187'
'Doe', '251'

如何通过查询或存储过程来做到这一点?

How can this be done with a query or stored procedure?

推荐答案

由于您尚未指定数据库，因此我给了您两个选择:

Since you haven't specified the DB I'm giving you two options:

使用MySql时，您只需使用GROUP_CONCAT()聚合函数.

With MySql you should simply use GROUP_CONCAT() aggregate function.

很显然，要想在MS DB上获得相同结果的最快方式(没有游标，没有合并...)是通过使用FOR XML PATH('')，而该XML元素只是省略了XML元素.

Obviously the fastest way (no cursors, no coalesce...) of getting the same result on MS DB is by using FOR XML PATH('') that simply omits XML elements.

SELECT
    u.Name,
    c1.UserId,
    (
        SELECT c2.Code + ','
        FROM Codes c2
        WHERE c2.UserId = c1.UserId
        ORDER BY c2.code
        FOR XML PATH('')
    ) as Codes
FROM Codes c1
    JOIN Users u
    ON (u.Id = c1.UserId)
GROUP BY c1.UserId, u.Name

其他替代方案

阅读本文，它解释了实现此目标的所有可能方法.

Other alternatives

Read this article, that explains all the possible ways of achieving this goal.

这篇关于MS SQL在一组行上的字符串连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！