从多个表连接多个列

Question

很抱歉，如果有人问了这个问题，搜索了一下，没有找到我想要的东西，但是如果它有链接的话。

我正在使用Oracle，并试图在不使用临时表的情况下跨多个表中的多个列聚合结果。例子如下：

Table: USERS
--------------
ID | USER_NAME
--------------
1  | Bob
2  | Joe
3  | Mary

Table: PROJECT_USERS
----------------------------------
USER_ID | PROJECT_ID | ACCESS_TYPE
----------------------------------
1       |123         |8
1       |456         |9
1       |789         |10
2       |123         |10
2       |456         |9
2       |789         |8
3       |123         |9
3       |456         |10
3       |789         |10

我已经能够使用LISTAGG来做一些成功的事情，比如使用如下的查询来查找某个特定项目输出中的用户：

SELECT
    LISTAGG(users.user_name, ',') WITHIN GROUP (ORDER BY users.user_name)
FROM
    users,
    project_users
WHERE
    project_users.user_id = users.id
    AND project_users.project_id = 123
GROUP BY ID
;

(抱歉，如果上面的语法稍有偏离，由于某些原因而模糊了实际的结构和数据，所以这不是我使用的确切查询)，它将输出：

Bob,Joe,Mary

但是，我希望输出的是以类似格式聚合的USERS.USER_NAME和PROJECT_USERS.ACCESS_TYPE的组合，也许这两个值由-

Bob-8,Joe-10,Mary-9

我可以得到个别的回报

SELECT users.user_name || '-' || project_users.access_type...

返回

Bob-8
Joe-9
Mary-10

我希望我能得到这些结果，但不幸的是，我还没能让它发挥作用。正如前面提到的，临时表马上就出来了，原因是我不想进入其中，尽管我确信这会使事情变得容易得多。使用SELECT in无法工作，我不认为，因为最终我希望能够在子查询中使用它，而我的理解(以及尝试它的有限经验)是，每次遍历都不会正确地迭代。也许我错了，只是做错了。

有什么建议吗？

谢谢!

Answer 1

看来你想要这样的东西。不知道为什么需要它(在本例中)，但它说明了所询问的方法。

with
     users ( id, user_name ) as (
       select 1, 'Bob'  from dual union all
       select 2, 'Joe'  from dual union all
       select 3, 'Mary' from dual
     ),
     project_users ( user_id, project_id, access_type ) as (
       select 1, 123,  8 from dual union all
       select 1, 456,  9 from dual union all
       select 1, 789, 10 from dual union all
       select 2, 123, 10 from dual union all
       select 2, 456,  9 from dual union all
       select 2, 789,  8 from dual union all
       select 3, 123,  9 from dual union all
       select 3, 456, 10 from dual union all
       select 3, 789, 10 from dual
     )
-- End of test data (not part of the SQL query). 
-- Query begins below this line.
select project_id,
       listagg(user_name || '-' || to_char(access_type), ',')
           within group (order by user_name) as comma_sep_list
from   users u join project_users p on u.id = p.user_id
group by project_id
;

PROJECT_ID COMMA_SEP_LIST
---------- --------------------
       123 Bob-8,Joe-10,Mary-9
       456 Bob-9,Joe-9,Mary-10
       789 Bob-10,Joe-8,Mary-10

3 rows selected.