與 Where 子句完全外連接

我需要一種方法來返回兩個表中的所有記錄但是有一個 where 條件ZT1我已經嘗試了下面的兩個查詢，但是查詢 1 忽略了 @TTBL 中 ZT1 中不存在的任何行，而查詢 2 忽略了 WHERE 條件我想把 ZT1 放在桌子上（之前它被改為或）

應該如何編寫我的查詢以完成我想要的來自@TTBL 的所有數據的結果以及來自 ZT1 的介於日期條件之間的數據

   Query 1
SELECT
   Employee = COALESCE(ZT1.Employee,Z.Name),
   COUNT(ZT1.sales) AS Salecount,
   SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
   SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
   SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
   ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
   ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
WHERE ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

--Query 2
SELECT
   Employee = COALESCE(ZT1.Employee,Z.Name),
   COUNT(ZT1.sales) AS Salecount,
   SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
   SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
   SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
   ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
   ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
OR ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

試一試這個查詢。我懷疑“WHERE”子句有效地將您的完全外連接變成了右連接。當 ZT1 中不存在記錄時，您的 where 子句顯然會評估為 false。為了理智起見，將 AND 子句括在括號中，並添加某種 OR 子句（我假設您應該在 @TTBL 中有一個名為 SaleDate 的欄位，並且您只想以一種方式獲取 SaleDate 在該範圍內的記錄或另一個），您應該希望看到這個問題消失。讓我知道事情的後續。

SELECT Employee = COALESCE(ZT1.Employee,Z.Name), COUNT(ZT1.sales) AS Salecount, SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal, SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale, SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin, ISNULL(Z.SaleTotal0,0) AS SaleTotal0, ISNULL(Z.SaleTotal1,0) AS SaleTotal1 FROM dbo.holdings AS ZT1 FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee WHERE (ZT1.[SaleDate] >= '20170601' AND ZT1.[SaleDate] <= '20170625') OR (Z.[SaleDate] >= '20170601' AND Z.[SaleDate] <= '20170625') GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

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