为什么这个左外部联接不包括左边的所有主键

Question

客户表共有 1000 个客户，其中 1500 个在 2016 财年下了订单。但我们希望显示所有客户及其在 2016 财年的订单总数，无论客户是否在该财年下了订单。但 SQL Server 2012 中的以下查询仅显示 1490。

我们在这里可能错过了什么？

SELECT c.CustomerID, count(*) AS TotalOrders
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.FiscalYear = '2016'
GROUP BY c.CustomerID

更新：

以下查询仅再返回 1 条记录 (1491( - 仍然缺少 9 条记录。

SELECT c.CustomerID, count(*) AS TotalOrders
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
                   AND o.FiscalYear = '2016'
GROUP BY c.CustomerID

Answer 1

你where子句正在将left outer join变成Inner join。

将其更改为AND：

SELECT c.CustomerID, count(o.CustomerID) AS TotalOrders
FROM Customers c
LEFT JOIN Orders o
    ON c.CustomerID = o.CustomerID
    AND o.FiscalYear = '2016'     -- Here
GROUP BY c.CustomerID

Answer 2

正确的 SQL 是：

SELECT c.CustomerID, count(o.CustomerID) AS TotalOrders,
       sum(count(o.CustomerID)) over () as TotalTotalOrders
FROM Customers c LEFT JOIN
     Orders o
     ON c.CustomerID = o.CustomerID AND o.FiscalYear = '2016'
GROUP BY c.CustomerID;

TotalTotalOrders应该是所有订单(或至少是具有有效客户 ID 的订单(。

Answer 3

这将列出所有客户，无论他们是否有任何订单，无论下订单的年份如何。然后，sum将计算 2016 年下的所有订单，忽略其余订单，并返回一个 intenger (即它永远不会为空(。

SELECT
   c.CustomerID
  ,sum(case when o.FiscalYear = '2016' then 1 else 0 end)  AS TotalOrders
FROM Customers c
LEFT JOIN Orders o
 ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID