sql - 左连接右表条件

Question

我正在尝试使用 LEFT JOIN 和右表上的条件，但我遇到了很多问题。

我有两个表:

1. 项目{project_id,start_date}
2. projectForeCast{project_id,year_number,month_number,hours}

我正在尝试获取上周打开的所有项目以及上个月记录的小时数。

SELECT      dbo.Project.PROJECT_ID, dbo.ProjectForeCast.HOURS AS F0
FROM         dbo.Project LEFT JOIN  dbo.ProjectForeCast ON dbo.Project.PROJECT_ID = dbo.ProjectForeCast.PROJECT_ID
WHERE   (dbo.ProjectForeCast.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
                      (dbo.ProjectForeCast.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
                      (DATEPART(WK,dbo.Project.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
                      (DATEPART(YYYY,dbo.Project.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))

它工作得很好，但如果该项目在上一个 month_number 中没有在 projectForeCast 中的记录，我根本不会得到该项目。在这种情况下，我想在 F0 列中得到一个空单元格或 null。这就是我尝试 LEFT JOIN 但没有成功的原因。

Answer 1

根据我以前的经验，我会像这样编写您的 SQL 查询:

SELECT p.PROJECT_ID, pfc.HOURS AS F0
FROM 
(   SELECT dbo.Project.PROJECT_ID FROM dbo.Project q 
    WHERE (DATEPART(WK,dbo.Project.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
    (DATEPART(YYYY,dbo.Project.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))
) p
LEFT JOIN  
(   SELECT dbo.ProjectForeCast.HOURS FROM dbo.ProjectForeCast 
    WHERE (dbo.ProjectForeCast.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
    (dbo.ProjectForeCast.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE()))))
) pfc
ON p.PROJECT_ID = pfc.PROJECT_ID

OR 使用表别名使 SQL 语句更具可读性:

SELECT p.PROJECT_ID, pfc.HOURS AS F0
FROM 
(   SELECT pr.PROJECT_ID FROM dbo.Project pr 
    WHERE (DATEPART(WK,pr.START_DATE) = DATEPART(WK, DATEADD(WK, - 1, GETDATE())))AND
    (DATEPART(YYYY,pr.START_DATE) = DATEPART(YYYY, DATEADD(WK, - 1, GETDATE())))
) p
LEFT JOIN  
(   SELECT pf.HOURS FROM dbo.ProjectForeCast pf
    WHERE (pf.YEAR_NUMBER = DATEPART(YYYY, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE())))) AND 
    (pf.MONTH_NUMBER = DATEPART(MM, DATEADD(MM, 0, DATEADD(WK, - 1, GETDATE()))))
) pfc
ON p.PROJECT_ID = pfc.PROJECT_ID

我认为您将通过上述查询获得正确的结果。