有条件的WHERE子句,向管理员显示所有项目或向员工显示分配的项目

我有一张PROJECTtable。用户被分配到不同的项目。用户还可以具有STAFF或ADMINISTRATOR角色。如果他们是STAFF，他们应该只看到分配给他们的项目。如果他们是ADMINISTRATOR，他们应该能够看到所有项目。

Project Table
-project_id (pk)

User Table
-user_id (pk)

Project User Table
-project_user_id (pk)
-project_id (fk)
-user_id (fk)

Role Table
-role_id (pk)
-rolename (STAFF or ADMINISTRATOR)

User Role Table
-user_role_id (pk)
-user_id (fk)
-role_id (fk)

现在，根据用户的角色(他们可以是工作人员或管理员)，我想向他们展示适当的项目。这就是我试图构建查询的方式，但在如何应用条件方面遇到了困难。

第1步)

--select all projects
select
    pu.project_id
from
   project p

步骤2)

--select all projects and the users assigned to them
    select
        pu.project_id
    from
       project p
    left join
       project_user pu
     on
       p.project_id = pu.project_id

第3步)

--select all projects and the users assigned to them and the users role
    select
        pu.project_id,
        pu.user_id,
        r.rolename
    from
       project p
    left join
       project_user pu
     on
       p.project_id = pu.project_id
    left join
       user_role ur
    on
       ur.user_id = pu.user_id
    inner join
       role r
    r.role_id = ur.role_id

这就是我现在被困的地方。如何应用条件来检查该用户是STAFF还是ADMINISTRATOR并选择合适的项目？

示例数据如下：

WITH
    projects AS
        (
            Select 'A'  "PROJECT_ID" From Dual Union All
            Select 'B'  From Dual Union All
            Select 'C'  From Dual Union All
            Select 'D'  From Dual Union All
            Select 'E'  From Dual Union All
            Select 'F'  From Dual Union All
            Select 'G'  From Dual Union All
            Select 'H'  From Dual Union All
            Select 'I'  From Dual 
        ),
    users AS
        (
            Select '1'  "USER_ID" From Dual Union All
            Select '2'  From Dual Union All
            Select '3'  From Dual Union All
            Select '4'  From Dual

        ),
    usr_projects AS
        (
            Select 'A_1' "PROJ_USER_ID", 'A' "PROJECT_ID", 1 "USER_ID" From Dual Union All
            Select 'B_1' "PROJ_USER_ID", 'B' "PROJECT_ID", 1 "USER_ID" From Dual Union All
            Select 'C_2' "PROJ_USER_ID", 'C' "PROJECT_ID", 2 "USER_ID" From Dual Union All
            Select 'D_3' "PROJ_USER_ID", 'D' "PROJECT_ID", 3 "USER_ID" From Dual Union All
            Select 'E_3' "PROJ_USER_ID", 'E' "PROJECT_ID", 3 "USER_ID" From Dual Union All  
            Select 'F_4' "PROJ_USER_ID", 'F' "PROJECT_ID", 4 "USER_ID" From Dual Union All
            Select 'G_2' "PROJ_USER_ID", 'G' "PROJECT_ID", 2 "USER_ID" From Dual Union All
            Select 'H_3' "PROJ_USER_ID", 'H' "PROJECT_ID", 3 "USER_ID" From Dual Union All
            Select 'I_1' "PROJ_USER_ID", 'I' "PROJECT_ID", 1 "USER_ID" From Dual        
        ),
    roles AS
        (
            Select 'R1' "ROLE_ID", 'ADMIN' "ROLE_NAME" From Dual Union All
            Select 'R2', 'STUFF'    From Dual
        ),
    usr_roles AS
        (
            Select '1_R1' "USER_ROLE_ID", 1 "USER_ID", 'R1' "ROLE_ID" From Dual Union All
            Select '2_R2' "USER_ROLE_ID", 2 "USER_ID", 'R2' "ROLE_ID" From Dual Union All
            Select '3_R2' "USER_ROLE_ID", 3 "USER_ID", 'R2' "ROLE_ID"  From Dual Union All
            Select '4_R2' "USER_ROLE_ID", 4 "USER_ID", 'R2' "ROLE_ID"  From Dual
        ),

..。您可以使用所需的所有数据创建CTE...

users_roles_projects AS
        (
            SELECT
                r.ROLE_NAME "ROLE_NAME",
                r.ROLE_ID "ROLE_ID",
                ur."USER_ROLE_ID",
                u.USER_ID "USER_ID",
                up.PROJ_USER_ID "PROJ_USER_ID",
                p.PROJECT_ID "PROJECT_ID"
            FROM
                usr_projects up
            INNER JOIN
                users u ON(u.USER_ID = up.USER_ID)
            INNER JOIN
                usr_roles ur ON(ur.USER_ID = u.USER_ID)
            INNER JOIN
                roles r ON(r.ROLE_ID = ur.ROLE_ID)
            INNER JOIN
                projects p ON(p.PROJECT_ID = up.PROJECT_ID)
            ORDER BY 
              r.ROLE_ID,
              u.USER_ID,
              p.PROJECT_ID      
        )

..。以下是生成的数据集。

/*  R e s u l t :
ROLE_NAME ROLE_ID USER_ROLE_ID USER_ID PROJ_USER_ID PROJECT_ID
--------- ------- ------------ ------- ------------ ----------
ADMIN     R1      1_R1         1       A_1          A          
ADMIN     R1      1_R1         1       B_1          B          
ADMIN     R1      1_R1         1       I_1          I          
STUFF     R2      2_R2         2       C_2          C          
STUFF     R2      2_R2         2       G_2          G          
STUFF     R2      3_R2         3       D_3          D          
STUFF     R2      3_R2         3       E_3          E          
STUFF     R2      3_R2         3       H_3          H          
STUFF     R2      4_R2         4       F_4          F      

* /

现在，您可以选择按活动用户过滤的数据，如下所示：

SELECT
    urp.*
FROM
    usr_roles ur
INNER JOIN
    users_roles_projects urp ON(urp.USER_ROLE_ID = ur.USER_ROLE_ID)
WHERE
    urp.USER_ID = :ActiveUser
ORDER BY 
              urp.ROLE_ID,
              urp.USER_ID,
              urp.PROJECT_ID

ActiveUser=1的结果为：

--  ROLE_NAME ROLE_ID USER_ROLE_ID USER_ID PROJ_USER_ID PROJECT_ID
--  --------- ------- ------------ ------- ------------ ----------
--  ADMIN     R1      1_R1         1       A_1          A          
--  ADMIN     R1      1_R1         1       B_1          B          
--  ADMIN     R1      1_R1         1       I_1          I

..。和用于：AcctiveUser=4

--  ROLE_NAME ROLE_ID USER_ROLE_ID USER_ID PROJ_USER_ID PROJECT_ID
--  --------- ------- ------------ ------- ------------ ----------
--  STUFF     R2      4_R2         4       F_4          F

如果希望管理员用户看到所有数据，则只需将WHERE子句更改为：

WHERE
    urp.USER_ID = :ActiveUser OR
    CASE WHEN EXISTS (SELECT  USER_ROLE_ID From usr_roles Where USER_ID = :ActiveUser And ROLE_ID IN(SELECT ROLE_ID From roles Where ROLE_NAME = 'ADMIN')) THEN 'ADM' ELSE 'STF' END = 'ADM'

致敬..。

有条件的WHERE子句,向管理员显示所有项目或向员工显示分配的项目

1条答案

相关问题

热门标签

最新问答