sql - 别名问题（无效的列名）

Question

我在弄清楚如何使别名正常工作时遇到了一些麻烦。我有一个复杂的查询，其中包含两次相同的表：HIFIS_Services. 一个被赋予别名HIFIS_Services_Goods，另一个被赋予别名HIFIS_Services_Stay。当然，它们都包含相同的列，所以我重命名HIFIS_Services_Goods.ServiceID为GoodsServiceID.

这是我的初始查询，效果很好：

SELECT 
vw_Stays.ClientID, 
vw_Stays.LastName, 
vw_Stays.FirstName, 
HIFIS_Places.Name AS ReferrerName, 
vw_Stays.RoomName AS MotelName, 
HIFIS_ReasonForGoodsActivityTypes.NameE AS ReasonForMotel, 
HIFIS_UnitOfMeasureTypes.NameE AS RoomType, 
vw_Stays.StayID, 
HIFIS_Services_Stay.DateStart AS StayStart, 
HIFIS_Services_Stay.OrganizationID AS StayOrganizationID, 
HIFIS_Services_Goods.OrganizationID AS GoodsOrganizationID, 
HIFIS_Services_Goods.ServiceID AS GoodsServiceID, 
HIFIS_Services_Goods.ServiceTypeID, 
HIFIS_Services_Goods.DateStart AS GoodsStart, 
vw_Stays.BedDateStart, 
vw_Stays.BedDateEnd, 
vw_Stays.StayDateStart, 
vw_Stays.CurrentAge, 
HIFIS_Goods.GoodTypeID, 
HIFIS_Goods.UnitPrice, 
HIFIS_Groups.GroupTypeID, 
HIFIS_People_Groups.PeopleGroupID, 
HIFIS_People_Groups.DateEnd AS FamilyEnd, 
HIFIS_People_Groups.DateStart AS FamilyStart, 
HIFIS_Services_Stay.FromReferrerPlaceID, 
HIFIS_Services_Goods.Comments


FROM   ((((((((((vw_Stays 
INNER JOIN HIFIS_Services AS HIFIS_Services_Stay ON vw_Stays.ServiceID=HIFIS_Services_Stay.ServiceID) 
LEFT OUTER JOIN HIFIS_Client_Services AS HIFIS_Client_Services_Goods ON vw_Stays.ClientID=HIFIS_Client_Services_Goods.ClientID) 
INNER JOIN HIFIS_Clients ON vw_Stays.ClientID=HIFIS_Clients.ClientID) 
LEFT OUTER JOIN HIFIS_Services AS HIFIS_Services_Goods ON HIFIS_Client_Services_Goods.ServiceID=HIFIS_Services_Goods.ServiceID) 
LEFT OUTER JOIN HIFIS_GoodsActivities ON HIFIS_Services_Goods.ServiceID=HIFIS_GoodsActivities.ServiceID) 
LEFT OUTER JOIN HIFIS_Goods ON HIFIS_GoodsActivities.GoodsActivitiesID=HIFIS_Goods.GoodsActivitiesID) 
LEFT OUTER JOIN HIFIS_ReasonForGoodsActivityTypes ON HIFIS_GoodsActivities.ReasonForGoodsActivityTypeID=HIFIS_ReasonForGoodsActivityTypes.ID) 
LEFT OUTER JOIN HIFIS_UnitOfMeasureTypes ON HIFIS_Goods.UnitOfMeasureTypeID=HIFIS_UnitOfMeasureTypes.ID) 
INNER JOIN HIFIS_Places ON HIFIS_Services_Stay.FromReferrerPlaceID=HIFIS_Places.PlaceID) 
LEFT OUTER JOIN HIFIS_People_Groups ON HIFIS_Clients.PersonID=HIFIS_People_Groups.PersonID) 
LEFT OUTER JOIN HIFIS_Groups ON HIFIS_People_Groups.GroupID=HIFIS_Groups.GroupID

WHERE  HIFIS_Services_Stay.OrganizationID=2 

AND PeopleGroupID = (
SELECT PeopleGroupID
    FROM HIFIS_People_Groups
    INNER JOIN HIFIS_Groups ON HIFIS_People_Groups.GroupID = HIFIS_Groups.GroupID
    WHERE GroupTypeID=1
)

但是，当我添加以下子句时，我得到Invalid column name: GoodsServiceID.

如果我只是使用ServiceID，我会得到Ambiguous column name。

如果我使用HIFIS_Services_Goods.ServiceID，我会得到Invalid object name: HIFIS_Services_Goods。

如果我尝试HIFIS_Services.ServiceID或HIFIS_Services.GoodsServiceID，我得到The multi-part identifier could not be bound。

AND GoodsServiceID = (
SELECT ServiceID
    FROM ((((HIFIS_Services_Goods
    INNER JOIN HIFIS_Client_Services HIFIS_Client_Services_Goods ON vw_Stays.ClientID=HIFIS_Client_Services_Goods.ClientID) 
    INNER JOIN HIFIS_Services HIFIS_Services_Goods ON HIFIS_Client_Services_Goods.ServiceID=HIFIS_Services_Goods.ServiceID)
    INNER JOIN HIFIS_GoodsActivities ON HIFIS_Services_Goods.ServiceID=HIFIS_GoodsActivities.ServiceID)
    INNER JOIN HIFIS_ReasonForGoodsActivityTypes ON HIFIS_GoodsActivities.ReasonForGoodsActivityTypeID=HIFIS_ReasonForGoodsActivityTypes.ID) 
    INNER JOIN HIFIS_UnitOfMeasureTypes ON HIFIS_Goods.UnitOfMeasureTypeID=HIFIS_UnitOfMeasureTypes.ID
WHERE HIFIS_Services_Goods.OrganizationID=2 
AND HIFIS_Goods.GoodTypeID=1000 
AND HIFIS_Services_Goods.ServiceTypeID=31
)

我究竟做错了什么？

标签： sql