用于跨角色检索通用用户的Oracle SQL查询

Question

我有一个表，其中有两列感兴趣的数据，如下所示：

ROLES                   USERS
--------------------    ------------------------------
WEBAPP1 User            adams16
WEBAPP1 User            adams23
WEBAPP1 User            adams56
WEBAPP1 User            ford1
WEBAPP1 User            clerk23
WEBAPP1 User            miller2
WEBAPP1 User            ward3
WEBAPP1 User            jones21
WEBAPP1 User            martin12
WEBAPP1 User            li21
WEBAPP2 User            ford1
WEBAPP2 User            aboud1
WEBAPP2 User            clerk23
WEBAPP2 User            abraham2
WEBAPP2 User            aceves1
WEBAPP2 User            adams16
WEBAPP2 User            adams23
WEBAPP2 User            adams56
WEBAPP2 User            geronimo2
WEBAPP3 User            algrove3
WEBAPP3 User            allen2
WEBAPP3 User            jones21
WEBAPP3 User            king15
WEBAPP3 User            king20
WEBAPP3 User            king41
WEBAPP3 User            turner1
WEBAPP3 User            villiers3
WEBAPP4 User            alonso4
WEBAPP4 User            alvarez3
WEBAPP4 User            alviso1

我可以找出哪些用户属于某个角色：

ROLES                   USERS
--------------------    ------------------------------
WEBAPP1 User            adams16, adams23, adams56, ford1, clerk23, miller2, ward3, jones21, martin12, li21
WEBAPP2 User            abraham2, aboud1, aceves1, adams16, adams23, adams56, clerk23, ford1, geronimo2
WEBAPP3 User            algrove3, allen2, jones21, king15, king20, king41, turner1, villiers3
WEBAPP4 User            alonso4, alvarez3, alviso1

用户有哪些角色：

USERS                   ROLES
--------------------    ------------------------------
aboud1                  WEBAPP2
abraham2                WEBAPP2
aceves1                 WEBAPP2
adams16                 WEBAPP1, WEBAPP2
adams23                 WEBAPP1, WEBAPP2
adams56                 WEBAPP1, WEBAPP2
...

我想要找到的是拥有最常见角色的用户列表。例如，

COMMON USERS                        COMMON ROLES
--------------------                ------------------------------
adams16, adams23, adams56, ford1    WEBAPP1, WEBAPP2
aboud1                              WEBAPP2
...

该列表将从具有相同共同角色的最大用户集和这些角色的列表开始，然后是第二大用户集和角色列表，依此类推。

我该如何实现这一点？我正在尝试寻找具有共同用户的角色组。任何帮助都将不胜感激。谢谢!

Answer 1

为此，您可以使用listagg()两次(或在早期版本的Oracle中使用wm_concat()。如下所示：

select roles, listagg(user, ', ') within group (order by user) as users
from (select user, listagg(role, ', ') within group (order by role) as roles
      from userroles
      group by user
     ) ur
group by roles;