子查询中的多个值

Question

我想概括一下底部的查询，以便获得所有项目和附加项目信息，其中我的子组具有特定的值，但无法使其工作，因为我无法获取[ projectFk]

SELECT 
     [HeadCount].[ID], 
     [LinkProjectAreaFk], 
     [Month1], 
     [Month2], 
     [Month3], 
     [Month4], 
     [Month5], 
     [Month6], 
     [Month7], 
     [Month8], 
     [Month9], 
     [Month10], 
     [Month11], 
     [Month12], 
     [HcYear], 
     [ApproveDirFk], 
     [ApproveDirDate], 
     [FreezeFk], 
     [freezeDate], 
     [CDSID],[FreezeTypeFk], 
     [ApproveDirTypeFk], 
     [SourcesFk], 
     [Project].[Title], 
     [Project].[Title] 
FROM 
    [HeadCount], 
     [Project] 
WHERE 
     [HeadCount].[LinkProjectAreaFk] 
    IN 
     (SELECT 
       [ID] 
     FROM 
       [LinkProject-Area] 
     WHERE 
     [SubgroupFk]=1) 
    and 
     [Project].[ID]=[LinkProject-Area].[ProjectFk] 

下面的查询工作，但只适用于一个项目如何我也从我的子查询得到projectFk，因为当我看着这件事，他们说这是不可能的，并与左外连接，我可以”吨得到它的工作在所有

SELECT 
     [HeadCount].[ID], 
     [LinkProjectAreaFk], 
     [Month1], 
     [Month2], 
     [Month3], 
     [Month4], 
     [Month5], 
     [Month6], 
     [Month7], 
     [Month8], 
     [Month9], 
     [Month10], 
     [Month11], 
     [Month12], 
     [HcYear], 
     [ApproveDirFk], 
     [ApproveDirDate], 
     [FreezeFk], 
     [freezeDate], 
     [CDSID], 
     [FreezeTypeFk], 
     [ApproveDirTypeFk], 
     [SourcesFk], 
     [Project].[Title], 
     [Project].[Title] 
FROM 
     [HeadCount], 
     [Project] 
WHERE 
     [HeadCount].[LinkProjectAreaFk] IN 
     (SELECT 
      [ID] 
     FROM 
      [LinkProject-Area] 
     WHERE 
      [ProjectFk]=90 
     and 
      [SubgroupFk]=1) 
     and 
      [Project].[ID]=90 

希望你能帮助我或指出我在正确的方向

亲切的问候

---

您好我一直试图用自己的方式，但它仍然不会理所应当： I'me越来越多部分标识符“MyCTE.ProjectFK”可能不受约束。和 无法绑定多部分标识符“MyCTE.ID”。当我查看这个错误时有什么错误与使用我的连接错误任何想法有关？

 ;WITH MyCTE (ID, ProjectFK) 
    AS 
    (  
    SELECT 
      ID, 
      ProjectFk  
    FROM 
      [LinkProject-Area]  
    WHERE 
      SubgroupFk = 1 
    ) 

    SELECT 
      * 
    FROM 
      [LinkProject-Area] 
    INNER JOIN 
      HeadCount ON [LinkProjectAreaFk] = MyCTE.ID 
    INNER JOIN 
      Project ON Project.ID = MyCTE.ProjectFK 

Answer 1

我想你会想组织这次查询作为这样的事：

SELECT ... 
FROM 
(
    SELECT ID, ProjectFk 
    FROM [LinkProject-Area] 
    WHERE SubgroupFk = 1 
) LinkProjectAreas 
INNER JOIN HeadCount ON LinkProjectAreaFK = LinkProjectAreas.ID 
INNER JOIN Project ON Project.ID = LinkProjectAreas.ProjectFK 

你也可以用公用表表达式（CTE）组织本：

;WITH LinkProjectAreas (ID, ProjectFK) AS 
(
    SELECT ID, ProjectFk 
    FROM [LinkProject-Area] 
    WHERE SubgroupFk = 1 
) 
SELECT ... 
FROM LinkProjectAreas 
INNER JOIN HeadCount ON LinkProjectAreaFK = LinkProjectAreas.ID 
INNER JOIN Project ON Project.ID = LinkProjectAreas.ProjectFK