时间戳

首页 > 解决方案 > 为 1 列中的用户返回多个角色而不是多行

sql - 为 1 列中的用户返回多个角色而不是多行

问题描述

SQL 服务器

我正在尝试为包含他们的名字、姓氏、电子邮件等的用户返回 1 行，以及包含在通过 UserRoles 连接的单独角色表中的所有角色。

User              ->     UserRoles         ->    Role
-------------            ---------------         ------------
Email                    UserEmail               Id
Firstname                RoleId                  Name
Lastname
Phone
IsActive


  declare @Email nvarchar(100);
  set @Email = 'testemail@gmail.com'
  
  SELECT [User].Email, Password, Username, Firstname, Lastname, Phone, IsActive, EmailVerified, IsBanned, BanStart, BanEnd, [Role].Name as Roles
  FROM dbo.[User], [UserRole], [Role] 
  WHERE [User].Email = @Email AND UserRole.UserEmail = [User].Email AND UserRole.RoleId = Role.Id

返回

Email               Username            Firstname Lastname Phone IsActive EmailVerified IsBanned BanStart BanEnd Roles     
testemail@gmail.com testemail@gmail.com daniel  brownn  3199999999  1   0   0   NULL    NULL    Expert
testemail@gmail.com testemail@gmail.com daniel  brownn  3199999999  1   0   0   NULL    NULL    User

不幸的是，对于具有 2 个角色的用户，这会返回正确的信息，它会返回 2 个单独的行。我的目标是在包含两个角色值的角色列中返回 1 行，其中包含逗号分隔值。这可能吗？

谢谢！

标签： sqlsql-serverdatabaserows

解决方案

您可以尝试使用JOIN子句和STRING_AGG方法。在这里您可以找到此方法的文档。您的查询结果如下：

SELECT
  [User].Email,
  Password,
  Username,
  Firstname,
  Lastname,
  Phone,
  IsActive,
  EmailVerified,
  IsBanned,
  BanStart,
  BanEnd,
  STRING_AGG([Role].Name, ', ') as Roles
FROM
  dbo.[User]
  JOIN [UserRole] ON [UserRole].UserEmail = [User].Email
  LEFT JOIN [Role] ON [Role].Id = [UserRole].RoleId
GROUP BY
  [User].Email,
  Password,
  Username,
  Firstname,
  Lastname,
  Phone,
  IsActive,
  EmailVerified,
  IsBanned,
  BanStart,
  BanEnd

请注意，这STRING_AGG是一个 t-sql 方法，但在 MySQL 中有一些等效的方法（如果我没记错的话GOUP_CONCAT 在这里你可以找到一些东西）

推荐阅读