sql - 使用 SQL STUFF 连接我无法分组的行

Question

我正在尝试获取特定应用的用户角色的逗号分隔列表。我以前用过 STUFF 和 FOR XML PATH('') 来解决这个问题，但我不知道在这种情况下我是否可以。这是显示我想做什么但不起作用的查询:

SELECT FName, LName, Email, Username, 
    STUFF((SELECT r.RoleDescription FROM Roles r2 WHERE r2.RoleID = r.RoleID FOR XML PATH('')),1,1,'') as RoleList
FROM Users u
INNER JOIN UserRole ur on u.UserID = ur.UserID
INNER JOIN Roles r on r.RoleID = ur.RoleID
WHERE ur.AppID = 506
GROUP BY FName, LName, Email, Username

这不起作用，因为我的子查询 where 子句包含一个不在 GROUP BY 中的列。但是，如果我按 RoleID 分组，我将无法在聚合函数中组合这些行。

还有其他方法吗？或者我可以修复查询以获得结果的方法？

Answer 1

OUTER APPLY 更整洁

SELECT FName, LName, Email, Username, 
    STUFF(x.csv,1,1,'') as RoleList
FROM Users u
INNER JOIN UserRole ur on u.UserID = ur.UserID
OUTER APPLY
    (
    SELECT
        ',' + r.RoleDescription
    FROM
        Roles r
    WHERE
        r.RoleID = ur.RoleID
    FOR XML PATH ('')
    ) x (csv)
WHERE ur.AppID = 506
GROUP BY FName, LName, Email, Username, x.csv

但是，您现在可能不需要分组，因为您不需要为每个加入的 Role 行重复

SELECT FName, LName, Email, Username, 
    STUFF(x.csv,1,1,'') as RoleList
FROM Users u
INNER JOIN UserRole ur on u.UserID = ur.UserID
OUTER APPLY
    (
    SELECT
        ',' + r.RoleDescription
    FROM
        Roles r
    WHERE
        r.RoleID = ur.RoleID
    FOR XML PATH ('')
    ) x (csv)
WHERE ur.AppID = 506

为了完整起见，对于内联子查询，DISTINCT 比 GROUP BY 效果更好，因为它应用到实际值的时间要晚得多。 RoleId 不是实际值的一部分，因此不会破坏 DISTINCT

更新实际上在 UserRole 上的过滤器，这意味着我们可以使用 CROSS APPLY

SELECT FName, LName, Email, Username, 
    STUFF(x.csv,1,1,'') as RoleList
FROM Users u
CROSS APPLY
    (
    SELECT
        ',' + r.RoleDescription
    FROM
        UserRole ur
        JOIN
        Roles r ON ur.RoleID = r.RoleID
    WHERE
        u.UserID = ur.UserID
    FOR XML PATH ('')
    ) x (csv)
WHERE u.UserID IN (Select UserID FROM UserRole WHERE AppID = 506)