TSQL Msg 1013“使用相关名称来区分它们." [英] TSQL Msg 1013 "Use correlation names to distinguish them."

问题描述

我查阅了很多建议，但在过去的两个小时里不知道如何解决这个问题.

I looked trough many suggestions and can't figure how to solve this one for the last two hours.

SET DATEFORMAT DMY

DECLARE @Source DATETIME = '01/01/2001'
DECLARE @Destenaition DATETIME = '01/01/2020'

SELECT ST.[Group],
       ST.Shop,
       SUM(ST.Purchased) AS Total,
       CHG.Charged
FROM   (SELECT Personals.Groups.[Name]      AS 'Group',
               Cards.vPurchases.PersonalID,
               Personals.Registry.[Name],
               SUM(Cards.vPurchases.Ammont) AS Purchased,
               Cards.vPurchases.ShopName    AS Shop
        FROM   Cards.vPurchases
               INNER JOIN Personals.Registry
                 ON Personals.Registry.Id = Cards.vPurchases.PersonalID
               INNER JOIN Personals.Groups
                 ON Personals.Registry.[Group] = Personals.Groups.Id
               INNER JOIN Personals.Groups
                 ON Personals.Groups.Id = CHG.GroupID
        WHERE  Cards.vPurchases.[TimeStamp] >= @Source
               AND Cards.vPurchases.[TimeStamp] <= @Destenaition
        GROUP  BY Cards.vPurchases.PersonalID,
                  Personals.Registry.[Name],
                  Personals.Groups.[Name],
                  Cards.vPurchases.ShopName) ST,
       (SELECT PG.Id                      AS GroupID,
               SUM(Cards.vCharges.Amount) AS Charged
        FROM   Cards.vCharges
               INNER JOIN Personals.Registry
                 ON Personals.Registry.Id = Cards.vCharges.PersonalID
               INNER JOIN Personals.Groups AS PG
                 ON Personals.Registry.[Group] = PG.Id
        WHERE  Cards.vCharges.[TimeStamp] >= @Source
               AND Cards.vCharges.[TimeStamp] <= @Destenaition
        GROUP  BY Personals.Groups.[Name]) AS CHG
GROUP  BY ST.Shop,
          ST.[Group]  

然后我收到此错误:

消息 1013，级别 16，状态 1，第 6 行对象Personals.Groups"和FROM 子句中的Personals.Groups"具有相同的公开名称.用相关名称以区分它们.

Msg 1013, Level 16, State 1, Line 6 The objects "Personals.Groups" and "Personals.Groups" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

谢谢.

推荐答案

您在第一个子查询中使用了表 Personals.Groups 两次.如果您真的想拥有表 Personals.Groups，则需要为它们提供一个别名，然后在查询的其余部分中使用该别名代替表名.

You are using the table Personals.Groups two times in the first sub query. If you really mean to have the table Personals.Groups you need to give them an alias that you then use instead of the table names in the rest of the query.

INNER JOIN Personals.Groups as PG1

和

INNER JOIN Personals.Groups as PG2

如果您只需要一个，您可以组合 on 子句以仅使用一个.

If you only need one you can combine the on clauses to use just one instead.

INNER JOIN Personals.Groups 
  ON Personals.Registry.[Group] = Personals.Groups.Id and
     Personals.Groups.Id = CHG.GroupID

这篇关于TSQL Msg 1013“使用相关名称来区分它们."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！