sql-server - SQL Server 2012 无法绑定(bind)多部分标识符

Question

我有 4 个表要加入 Adventureworks 2012。我不明白为什么会收到“无法绑定(bind)错误”。

Msg 4104, Level 16, State 1, Line 7

The multi-part identifier "Production.ProductCategory.Name" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "Production.ProductCategory.Name" could not be bound.

SELECT
    COUNT(WorkOrderID) AS TotalWorkOrders, 
    [Production].[ProductCategory].[Name]
FROM [Production].[WorkOrder] WO
INNER JOIN [Production].[Product] P ON WO.[ProductID] = P.[ProductID]
INNER JOIN [Production].[ProductSubcategory] PS ON PS.[ProductSubcategoryID] = P.[ProductSubcategoryID]
INNER JOIN [Production].[ProductCategory] PC ON PC.[ProductCategoryID] = PS.[ProductCategoryID]
WHERE WO.[StartDate] >= '1999—03-08' AND WO.[StartDate] <= '2008-05-02'
GROUP BY [Production].[ProductCategory].[Name]

Answer 1

您的查询已为表[Production].[ProductCategory]指定别名PC。您需要在查询的其余部分中使用它:

SELECT COUNT(WO.WorkOrderID) AS TotalWorkOrders, 
       PC.[Name]
FROM [Production].[WorkOrder] WO
     INNER JOIN [Production].[Product] P ON WO.[ProductID] = P.[ProductID]
     INNER JOIN [Production].[ProductSubcategory] PS ON PS.[ProductSubcategoryID] = P.[ProductSubcategoryID]
     INNER JOIN [Production].[ProductCategory] PC ON PC.[ProductCategoryID] = PS.[ProductCategoryID]
WHERE WO.[StartDate] >= '1999—03-08' AND WO.[StartDate] <= '2008-05-02'
GROUP BY PC.[Name];

一旦为表指定了别名，您就需要引用该别名而不是原始表名称。