sql - 员工角色的连续行

Question

我正在使用 SQL Server。寻找与其他数据库中提供的类似功能GROUP_CONCAT或功能。listagg似乎STUFF为连接行提供了功能。这是我的尝试。

SELECT Employee.Id, Employee.First,
    STUFF((
        SELECT '' + Role.Id
        FROM Role
        WHERE EmployeeRole.RoleId = Role.Id
        FOR XML PATH('')), 1, 0, '') AS RoleIds,
From Employee
  INNER JOIN EmployeeRole ON Employee.Id = EmployeeRole.EmployeeId
  INNER JOIN Role ON EmployeeRole.RoleId = Role.Id
  ORDER BY Employee.Id;

员工

+----+-------+------+
| Id | First | Last |
+----+-------+------+
|  1 | John  | Doe  |
|  2 | Jane  | Doe  |
+----+-------+------+

角色

+----+-------+
| Id | Name  |
+----+-------+
|  1 | Role1 |
|  2 | Role2 |
|  3 | Role3 |
|  4 | Role4 |
+----+-------+

员工角色

+------------+--------+
| EmployeeId | RoleId |
+------------+--------+
|          1 |      1 |
|          1 |      2 |
|          2 |      2 |
|          2 |      3 |
|          2 |      4 |
+------------+--------+

预期产出

+------------+-------+---------+-------------------+
| EmployeeId | First | RoleIds |     RoleNames     |
+------------+-------+---------+-------------------+
|          1 | John  | 1,2     | Role1,Role2       |
|          2 | Jane  | 2,3,4   | Role2,Role3,Role4 |
+------------+-------+---------+-------------------+

标签： sqlsql-serverstringinner-joinaggregate-functions

Answer 1

在 SQLServer 2017 中，您可以使用聚合函数STRING_AGG()：

SELECT 
    e.Id EmployeeId, 
    e.First,
    STRING_AGG(r.Id, ',') WITHIN GROUP(ORDER BY r.Id) RoleIds,
    STRING_AGG(r.Name, ',') WITHIN GROUP(ORDER BY r.Id) RoleNames
FROM Employee e
INNER JOIN EmployeeRole er ON e.Id = er.EmployeeId
INNER JOIN Role r ON er.RoleId = r.Id
GROUP BY e.Id, e.First
ORDER BY e.Id;

注意：表别名使查询更简洁，更易于阅读。我修改了查询以使用它们。