为表多次指定了第 xxx 列

2022-01-14 00:00:00 join alias db2 tsql sql-server

我正在尝试使 db2 简单查询适应 SQL SERVER.此查询在 db2 上运行良好

I am trying to adapt a db2 simple query to SQL SERVER. This query is working fine on db2 

select * 
from pb_console.users u 
join ( 
     select * from pb_console.users_user_role j join  
     pb_console.users_roles r on j.role_id = r.role_id) as jj 
on jj.user_id = u.user_id

在 sql server 上失败并出现错误:

on sql server it fails with error:

The column 'ROLE_ID' was specified multiple times for 'jj'

我已尝试将角色 _id 从联接的左表中删除为:

I have tried removing role _id from the left table of the join as:

select * from pb_console.users u join ( 
    select user_id, role_rif from 
    pb_console.users_user_role j join (select role_id, role_name from 
pb_console.users_roles) r 
on 
j.role_id = r.role_id) as jj on jj.user_id = u.user_id

但导致.

The column 'role_id' was specified multiple times for 'jj'.

我也尝试为第一个 role_id 使用不同的别名，但没有成功.

I have also tried using a different alias for the first role_id, with no success.

我该如何解决这个问题?

How can I fix this?

推荐答案

由于 ColumnNameROLE_ID 存在于两个表 pb_console.users_user_role &pb_console.users_roles 所以尝试指定只需要的列，如下所示

The error is raised because the ColumnNameROLE_ID is present in both the table pb_console.users_user_role & pb_console.users_roles so try to specify the columns that are only required as below

SELECT * 
FROM pb_console.users u 
join ( 
     SELECT J.RoleID,J.User_ID FROM pb_console.users_user_role j 
     JOIN pb_console.users_roles r ON j.role_id = r.role_id) AS jj 
on jj.user_id = u.user_id

相关文章