tsql - View 中的一对多 SELECT 语句

Question

数据库中有3个表:

Users (UserID, UserName), Roles(RoleID, RoleName) and UserRoles(UserID, RoleID)

如何在具有 IsAdmin 列的 Users 表上创建 View ，这是一个模型:

CREATE VIEW UsersView AS
    SELECT
        u.UserID,
        u.UserName,
        CASE WHEN ur.RoleID IS NULL THEN (0) ELSE (1) END AS IsAdmin
    FROM Users AS u
        LEFT JOIN Roles AS r ON r.RoleName = N'Admins'
        LEFT JOIN UserRoles AS ur ON  ur.UserID = u.UserID
                                  AND ur.RoleID = r.RoleID

IsAdmin 应该是 (1) 如果用户是“管理员”用户角色，并且 (0) 如果他不是

Answer 1

尝试:

  CREATE VIEW UsersView AS 
    SELECT 
        u.UserID, 
        u.UserName, 
        Case When Exists
           (Select * from userRoles ur
                Join Roles r On r.RoleId = ur.Roleid
            Where ur.userId = u.UserId
               And r.RoleName = '"Admins') 
          Then 1 Else 0 End IsAdmin 
    FROM Users u