问题描述

我在确定某种情况所需的 SQL 查询时遇到了困难.

I'm having a rough time figuring out the SQL query I need for a situation.

我有一个项目，该项目为用户提供工作室级别的用户角色，并且每个项目都具有覆盖/覆盖工作室级别角色的项目级别角色.所有角色都是在工作室级别定义的，但只有部分角色在项目级别定义(主要是与对应的工作室级别角色具有不同值的角色)

I have a project that has studio level user roles for a user, and each project has project level roles that overlay/override the studio level roles. All roles are defined at the studio level, but only some roles are defined at the project level (mainly roles that have different values than their corresponding studio level role)

g_studio_UsersInRole

userId  roleId  value
1       1       TRUE
1       2       TRUE
1       3       TRUE
2       1       FALSE

g_project_UsersInRole

userId  roleId  value    projectId
1       2       FALSE    1
2       1       TRUE     1

对于给定的项目 ID，我需要一个将项目角色覆盖在工作室角色上的查询.棘手的部分是避免重复的工作室级别角色.我需要项目级角色(如果有)来主导.

I need a query that overlays the project roles over the studio roles for a given project Id. The tricky part is avoiding the duplicate studio level role. I need the project level roles (if any) to dominate.

我一直在使用 Unions，但我不知道如何避免重复.

I've been playing with Unions, but I can't figure out how to avoid the duplicates.

基本上我需要以下结果:

Basically I need the following results:

userId  roleId  value
1       1       TRUE
1       2       FALSE
1       3       TRUE
2       1       TRUE

哪里

• userId 为 1，roleId 为 2 的值为 False
• userId 为 2，roleId 为 1 的值为 True

如项目级别所示

我以为我已经接近这个查询了，但重复项仍然存在:

I thought I was close with this query, but the duplicates are still present:

;With roles As
(
SELECT     UserId, Value, RoleId
                       FROM          dbo.g_project_UsersInRole
                       WHERE      (ProjectId = 1)
                       UNION
                       SELECT     UserId, Value, RoleId
                       FROM         dbo.g_studio_UsersInRole)

SELECT     roles.RoleId, Value, UserId
FROM        roles
RIGHT JOIN (SELECT DISTINCT RoleId FROM roles) AS distinctRoles
ON distinctRoles.RoleId = roles.RoleId

推荐答案

您不需要联合.只需从工作室左加入项目，然后使用合并

You don't need a union. Just a left join to project from studio and then use coalesce

以下

WITH g_studio_UsersInRole AS  --Sampledata
(
    SELECT 1  userId        ,1       roleId   , 'TRUE' value
    UNION SELECT 1,       2,       'TRUE'
    UNION SELECT 1,       3,       'TRUE'
    UNION SELECT 2,       1,       'FALSE')
, g_project_UsersInRole as --Sampledata
(
    SELECT 1 userId  ,       2  roleId  ,     'FALSE'  value ,     1 projectId
    UNION SELECT 2,       1,       'TRUE',     1
)

SELECT 
    sRole.userId,
    sRole.roleId,
    COALESCE(pRole.Value,sRole.value)  as value
FROM 
    g_studio_UsersInRole sRole
    LEFT JOIN g_project_UsersInRole  pRole
    ON sRole.userId = pRole.userId
      and sRole.roleId = pRole.roleId
     and pRole.ProjectId = 1

返回如下结果

userId      roleId      value
----------- ----------- -----
1           1           TRUE
1           2           FALSE
1           3           TRUE
2           1           TRUE

这篇关于一列的 SQL Server 不同联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！