给定一个特定的用户，我希望所有的用户至少拥有这个特定用户所拥有的所有角色。

Question

假设我有user_role表，其中有user和role列。

| User | Role |
---------------
| 100  | 10   |
| 101  | 10   |
| 101  | 11   |
| 102  | 11   |

我想要编写一个查询，它将返回具有相同或较小角色的用户。例如：

• 为用户100返回用户100
• 为用户101返回用户100,101,102
• 为用户102返回用户102

业务需求：假设用户X只属于亚洲集团。因此，X应该对只属于亚洲集团的用户具有访问权限。但如果说Y属于亚洲和欧洲集团。因此Y应该对属于以下用户的用户具有访问权限：

• 只限于亚洲集团
• 欧洲集团
• 只限于亚洲及欧洲集团

现在，X不应该访问Y的数据，因为X不属于Y所属的所有组。同样，假设Z属于亚洲、欧洲和美洲。因此，Z应该访问所有的数据X，Y和Z，但相反不是真的。

我的初始SQL:

select distinct(user) from user_role where role in
(select role from user_role where user=?);

上面的查询返回所有共享的用户--至少一个公共组，而不是所有公共组。

有人能帮我举一个SQL示例吗？

Answer 1

这可以用更少的努力来完成。这个想法是让特定用户的角色加入角色，然后只过滤那些在特定用户角色中找到所有角色的用户：

;with c as(select roleid from userroles where userid=100)
select r.userid from userroles r left join c on r.roleid = c.roleid
group by r.userid
having sum(case when c.roleid is null then 1 else 0 end) = 0

小提琴http://sqlfiddle.com/#!6/bca579/7

Answer 2

试试这个：

-- Create a CTE that will help us know the number of roles any user have.
;WITH CTE (UserId, RoleId, NumberOfRoles)
AS (
SELECT T1.UserId, RoleId, NumberOfRoles
FROM UsersToRoles T1 INNER JOIN
(
-- Derived table is needed so that we can have 
-- the user, he's roleId and he's number of roles in the CTE
SELECT UserId, COUNT(RoleId) As NumberOfRoles
FROM UsersToRoles 
GROUP BY UserId  
) T2 ON(T1.UserId =  T2.UserId)
)

-- We join the CTE with itself on the RoleId to get only users that have the same roles, 
-- and on the NumberOfRoles to ensure that the users we get back have at least the nomber of roles as the user we testing.
SELECT DISTINCT T1.UserId
FROM CTE T1 
INNER JOIN CTE T2 ON(T1.RoleId = T2.RoleId AND T1.NumberOfRoles <= T2.NumberOfRoles)
WHERE T2.UserId = @UserId

在这个sql小提琴中自己玩它。

常用表表达式是Server 2008中引入的一个概念。基本上，您定义了一个select语句，其余的sql可以引用它，就好像它是一个视图一样。

在本例中，您可以将此CTE编写为视图，它将给出相同的结果。