Oracle 非垂直模式下的数据表示抛出 “ORA-00918: 定义模糊的列”

嗨，团队

可复制数据请参考Live SQL link。

获取有关分配给用户的部门的信息:

SELECT user_login, dept_id, dept_name
FROM users, dept
WHERE users.user_key=dept.user_key;

USER_LOGIN       DEPT_ID DEPT_NAME     
------------- ---------- --------------
User1                  1 Physics       
User1                  2 Maths         
User2                  3 Physics       
User2                  4 Maths

我想以以下格式呈现输出。

USER_LOGIN       DEPT_ID_1 DEPT_NAME_1 DEPT_ID_2     DEPT_NAME_2
-------------    ------------   ------------ -----------   -------------    
User1            1  Physics  2       Maths 
User2            3  Physics  4       Maths

这让我 “ORA-00918: 定义模糊的列” 异常。

select user_login,
 max( decode(rn,1,dept_id) ) c1_1,
 max( decode(rn,1,dept_name) ) c2_1,
 max( decode(rn,2,dept_id) ) c1_2,
 max( decode(rn,2,dept_name) ) c2_2
from (
        select  users.user_login, 
                dept.dept_id, 
                dept.dept_name, 
                row_number() over (partition by user_login order by rowid) rn
        from users 
            JOIN dept ON users.user_key=dept.user_key
     )
group by user_login;

谢谢!

问题就在这里:

row_number() over (
  partition by user_login 
  order by rowid
)

你已经访问了两张表。数据库不知道从哪一个获取rowid!

您可以通过使用其中一个表的名称/别名前缀rowid来解决此问题。

但是rowid订购是个坏主意。最好在表格中挑选一个真实的列。你可以使用pivot子句，节省你输入所有这些最大表达式:

with rws as (
  select users.user_login, 
         dept.dept_id, 
         dept.dept_name, 
         row_number() over (
           partition by user_login 
           order by dept_id
         ) rn
  from users 
  JOIN dept 
  ON users.user_key=dept.user_key
)
  select * from rws
  pivot (
    max(dept_id) id, max (dept_name) name 
    for rn in ( 1, 2 )
  );

USER_LOGIN   1_ID   1_NAME    2_ID   2_NAME   
User1           1   Physics      2   Maths     
User2           3   Physics      4   Maths 

如果您想了解更多信息，请参阅https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot