使用 ON 子句時如何使用 WHERE 條件

我看過類似的文章並嘗試過，但似乎有效。在下面對 MSSQL 2015 數據庫的查詢中，我需要過濾掉非活動使用者。也就是說，只返回 ‘Active’ = ‘1’ 的結果。我試過：

ON Users.[Name] = Skills.[Name] where Users.[Active] = 1—- 關鍵字“LEFT”附近的語法不正確。

ON Users.[Name] = Skills.[Name] where Users.[Active] = '1'—- 關鍵字“LEFT”附近的語法不正確。

ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1'—- 查詢被執行但顯示所有結果。

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
   ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
   ON Users.[Name] = WeekTimeslip.[employeename] 
LEFT JOIN [master].[dbo].[Project] AS Project
   ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
   ON Users.[Name] = Utilisation.[employeename]
ORDER by Users.[Name]

你能告訴我我錯過了什麼嗎？

您可以像這樣將過濾器放入您的聯接中：

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
   ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
   ON Users.[Name] = WeekTimeslip.[employeename] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[Project] AS Project
   ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
   ON Users.[Name] = Utilisation.[employeename] AND Users.[Active] = '1'
ORDER by Users.[Name]

或者只是把它放在這樣的 where 子句中：

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
   ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
   ON Users.[Name] = WeekTimeslip.[employeename] 
LEFT JOIN [master].[dbo].[Project] AS Project
   ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
   ON Users.[Name] = Utilisation.[employeename]
WHERE Users.[Active] = '1'
ORDER by Users.[Name]

通常，SQL 會生成相同的執行計劃，而優化過程會做幾乎相同的事情。

引用自：https://dba.stackexchange.com/questions/263926