时间戳

首页 > 解决方案 > 我已经指定了不同的表名以避免歧义仍然存在错误

sql - 我已经指定了不同的表名以避免歧义仍然存在错误

问题描述

With tbl_name as 
     (Select top(1000) * 
      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(T.EmployeeNumber) as EmployeeNum from tbl_name

我得到的错误： -

消息 8156，级别 16，状态 1，第 24 行 为“tbl_name”多次指定了“EmployeeNumber”列。

标签： sqlsql-servertsqlsql-server-2012

解决方案

问题是您在联接中的两个表中都有相同的列。而不是SELECT TOP(1000) *从正确的表中指定列名。如果 2 个表具有相同的列名，并且您在结果集中需要这两个表，请为其中一个列指定不同的别名。像这样的东西

WITH tbl_name
     AS (SELECT TOP (1000) 
            E_EmployeeNumber = E.EmployeeNumber ,
            T_EmployeeNumber = T.EmployeeNumber
         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
            (T_EmployeeNumber) AS EmployeeNum
     FROM tbl_name;

此外，查看查询，以下内容也将比您当前的查询更好

SELECT
        *
        FROM tblEmployee E
            WHERE EXISTS
            (
                SELECT 1 FROM tblTransaction T WHERE EmployeeNumber = E.EmployeeNumber
            )

这将以更好的性能给出相同的结果

推荐阅读