SQL连接和聚合几个表

I have the following tables:

我有以下表格:

users
 - userid
 - real name
 - other stuff

roles
 - roleid
 - description
 - other stuff

functions
 - functionid
 - description

screens
 - screenid
 - description

A user can have multiple roles, as controlled by the table

用户可以有多个角色，由表控制。

user_roles
 - userid
 - roleid

Each role can have either edit, view or no access to a given function as controlled by the table

每个角色可以编辑、查看或不访问由表控制的给定函数

role_functions
- roleid
- functionid
- status

status is 'E' if the role has edit permission on that function, 'V' if the role has view permission on that function, and there can either be no record, or one with a 'N' status if the role has no permission on that function.

如果角色对该函数有编辑权限，那么状态为E;如果角色对该函数有视图权限，那么状态为V;如果角色对该函数没有权限，则状态为N。

Lastly a function has multiple screens, as controlled by the table

最后，函数有多个屏幕，由表控制

function_screens
 - functionid
 - screenid

It's kind of a confusing mess, I know, but the requirements for the roles, functions, and screens come from different business units so I can't simplify it. However, what I need is a query that I can give to the QA department and others that given a userid, they can list all the screens, and whether they have Edit, View or No access to that screen. If that user belongs to one role that gives them 'E' permission to a function that includes a screen, and another role that gives them 'V' permission to a function that includes the same screen, then their permission to that screen is 'E'.

我知道这有点混乱，但是角色、功能和屏幕的需求来自不同的业务单元，所以我不能简化它。但是，我需要的是一个查询，我可以提供给QA部门和其他具有userid的人，他们可以列出所有屏幕，以及他们是否有编辑、查看或没有访问该屏幕的权限。如果该用户属于一个角色，该角色向包含屏幕的函数授予“E”权限，而另一个角色向包含相同屏幕的函数授予“V”权限，那么他们对该屏幕的权限是“E”。

Right now I'm accomplishing all these lookups using a bunch of Java code with Maps of Maps, but I'm wondering if there is a simpler way to do it in a SQL script.

现在，我正在使用一堆映射映射的Java代码实现所有这些查找，但我想知道是否有一种更简单的方法可以在SQL脚本中执行。

1 个解决方案

#1

Try this:

试试这个:

select s.screenid, s.description
, CASE MAX(CASE rf.status WHEN 'E' THEN 2 WHEN 'V' THEN 1 ELSE 0 END)
    WHEN 2 THEN 'E'
    WHEN 1 THEN 'V'
    ELSE 'N' END as status
from user_roles ur
join role_functions rf on rf.roleid = ur.roleid
join function_screens fs on fs.functionid = rf.functionid
join screens s on s.screenid = fs.screenid
where ur.userid = :theuser
group by s.screenid, s.description
order by s.screenid

The 2 nested cases convert E, V and N to 2, 1 and 0 and back so that MAX can be used to get the "highest" status.

这两个嵌套的情况将E、V和N转换为2、1和0，然后返回，以便使用MAX获得“最高”状态。

#1