sql server语言 - sql Join视图-重复字段

Question

我试图加入两个视图，我已经尝试别名下的cAuditNumber字段StkSalesUpdated视图AuditNumber1，但我仍然得到以下消息:

Msg 4506, Level 16, State 1, Procedure KFF_Sales_Data_Updated, Line 3每个视图或函数中的列名必须是唯一的。列名视图中的'cAuditNumber'或函数'KFF_Sales_Data_Updated'为

下面是我的SQL语句:

CREATE VIEW KFF_Sales_Data_Updated
AS
SELECT CustSalesUpdated.cAuditNumber
   ,CustSalesUpdated.Account
   ,CustSalesUpdated.cAuditNumber
   ,CustSalesUpdated.Name
   ,StkSalesUpdated.cAuditNumber as AuditNumber1
   ,StkSalesUpdated.Code
   ,StkSalesUpdated.Credit
   ,StkSalesUpdated.Debit
   ,StkSalesUpdated.Description_1
   ,StkSalesUpdated.Id
   ,StkSalesUpdated.ItemGroup
   ,StkSalesUpdated.Quantity
   ,StkSalesUpdated.Reference
   ,StkSalesUpdated.TxDate
FROM CustSalesUpdated
INNER JOIN StkSalesUpdated
ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber

Answer 1

在位置1和3有两个cauditnumber实例，您需要别名或删除一个。

CREATE VIEW KFF_Sales_Data_Updated
AS
SELECT CustSalesUpdated.cAuditNumber -- HERE
   ,CustSalesUpdated.Account
   ,CustSalesUpdated.cAuditNumber --HERE
   ,CustSalesUpdated.Name
   ,StkSalesUpdated.cAuditNumber as AuditNumber1
   ,StkSalesUpdated.Code
   ,StkSalesUpdated.Credit
   ,StkSalesUpdated.Debit
   ,StkSalesUpdated.Description_1
   ,StkSalesUpdated.Id
   ,StkSalesUpdated.ItemGroup
   ,StkSalesUpdated.Quantity
   ,StkSalesUpdated.Reference
   ,StkSalesUpdated.TxDate
FROM CustSalesUpdated
INNER JOIN StkSalesUpdated
ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber

Answer 2

可能不是因为您的重命名或连接，而是因为您从同一表中选择了两次?(select语句的前3行)

SELECT CustSalesUpdated.cAuditNumber  
,CustSalesUpdated.Account
,CustSalesUpdated.cAuditNumber

Answer 3

在SELECT LIST中多次指定相同的列名。为other提供替代名称将解决您的问题。

CREATE VIEW KFF_Sales_Data_Updated
    AS
    SELECT CustSalesUpdated.cAuditNumber
       ,CustSalesUpdated.Account
       ,CustSalesUpdated.cAuditNumber as cAuditNumber_2
       ,CustSalesUpdated.Name
       ,StkSalesUpdated.cAuditNumber as AuditNumber1
       ,StkSalesUpdated.Code
       ,StkSalesUpdated.Credit
       ,StkSalesUpdated.Debit
       ,StkSalesUpdated.Description_1
       ,StkSalesUpdated.Id
       ,StkSalesUpdated.ItemGroup
       ,StkSalesUpdated.Quantity
       ,StkSalesUpdated.Reference
       ,StkSalesUpdated.TxDate
    FROM CustSalesUpdated
    INNER JOIN StkSalesUpdated
    ON StkSalesUpdated.cAuditNumber = CustSalesUpdated.cAuditNumber