为表多次指定了xxx列 [英] The column xxx was specified multiple times for table
本文介绍了为表多次指定了xxx列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使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服务器上,它失败并出现错误:
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.
我该如何解决?
推荐答案
由于在表pb_console.users_user_role & pb_console.users_roles
中都存在ColumnName
ROLE_ID ,因此引发错误.只需要如下
The error is raised because the ColumnName
ROLE_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
这篇关于为表多次指定了xxx列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文