SQL指定表别名避免字段歧义仍报错,EmployeeNumber列重复指定如何解决
Msg 8156, Level 16, State 1, Line 24 The column 'EmployeeNumber' was specified multiple times for 'tbl_name'.
(错误含义:为'tbl_name'定义的字段中'EmployeeNumber'出现多次)
错误原因
- 核心问题出在CTE的
SELECT *写法:你关联的两张表tblEmployee和tblTransaction都存在名为EmployeeNumber的字段,使用*会同时返回两张表的该字段,而CTE要求所有字段名必须唯一,因此在生成tbl_name临时结果集阶段就触发了重复字段错误,还未执行到外层的查询逻辑。 - 你为表设置的别名仅在CTE内部的JOIN逻辑中生效,无法解决CTE输出字段重名的问题;同时你外层查询使用的
T.EmployeeNumber写法也不合法,T是CTE内部的表别名,作用域仅在CTE括号内,外层查询无法访问该别名。
修复方法
提供三种可选择的方案:
方案1:明确指定CTE返回的字段(最推荐)
不需要的字段不返回,避免拉取冗余字段的同时解决重名问题,如果你只需要用到tblTransaction的字段,直接指定查询T.*即可:
With tbl_name as (Select top(1000) T.* from tblEmployee as E right join tblTransaction as T on E.EmployeeNumber = T.EmployeeNumber where E.EmployeeNumber is null order by T.EmployeeNumber) Select Distinct(EmployeeNumber) as EmployeeNum from tbl_name
方案2:为同名字段设置单独别名
如果确实需要用到两张表的字段,手动给重名字段设置不同别名即可:
With tbl_name as (Select top(1000) E.EmployeeNumber as E_EmployeeNumber, T.* from tblEmployee as E right join tblTransaction as T on E.EmployeeNumber = T.EmployeeNumber where E.EmployeeNumber is null order by T.EmployeeNumber) Select Distinct(EmployeeNumber) as EmployeeNum from tbl_name
方案3:简化逻辑省略CTE
你的需求只是查询关联后不存在的EmployeeNumber去重结果,可直接省略CTE简化语句:
Select Distinct TOP(1000) T.EmployeeNumber as EmployeeNum from tblEmployee as E right join tblTransaction as T on E.EmployeeNumber = T.EmployeeNumber where E.EmployeeNumber is null order by T.EmployeeNumber
内容的提问来源于stack exchange,提问作者Rishav Ranjan
相关产品推荐
相关产品推荐

