sql - 为什么这个查询不能正常工作？

我的 MasterSales 表看起来像这样

SalesDate | Category | Total
-----------------------------
1/1/2000    01          100
1/1/2000    02          110
1/2/2000    01          80
1/2/2000    03          20

我的 Category 表看起来像这样



我的查询如下所示:

SELECT m.SalesDate, c.Name, SUM(ISNULL(m.Total,0)) AS TotalSales
FROM MasterSales m
LEFT JOIN Category c ON c.ID = m.Category
WHERE m.SalesDate BETWEEN '1/1/2000' AND '1/2/2000'

我想要的结果是这样的:

SalesDate | Name | TotalSales
------------------------------
1/1/2000    A      100
1/1/2000    B      110
1/1/2000    C      0
1/1/2000    D      0
1/2/2000    A      80
1/2/2000    B      0
1/2/2000    C      20
1/2/2000    D      0

但我得到的结果是这样的:

SalesDate | Name | TotalSales
------------------------------
1/1/2000    A      100
1/1/2000    B      110
1/2/2000    A      80
1/2/2000    C      20

我已经尝试使用 RIGHT JOIN 而不是 LEFT JOIN 并在 FROM 子句上切换表，但结果仍然相同。谁能帮我解释一下为什么它不能正常工作？

附言:我使用的是 SQL Server 2005(如果重要的话)

最佳答案

这是我的答案

WITH MasterSales (SalesDate, Category, Total) AS (
  SELECT       '1/1/2000','01',100
  UNION SELECT '1/1/2000','02',110
  UNION SELECT '1/2/2000','01',80
  UNION SELECT '1/2/2000','03',20
), Category (ID, Name) AS (
  SELECT       '01','A'
  UNION SELECT '02','B'
  UNION SELECT '03','C'
  UNION SELECT '04','D'
), getDates AS (
  SELECT DISTINCT SalesDate
  FROM MasterSales
  WHERE SalesDate BETWEEN '1/1/2000' AND '1/2/2000'
)

SELECT gD.SalesDate, C.Name, SUM(ISNULL(MS.Total,0)) AS TotalSales
FROM getDates AS gD
CROSS JOIN Category AS C
LEFT JOIN MasterSales AS MS
    ON MS.Category = C.ID
    AND MS.SalesDate = gD.SalesDate
GROUP BY gD.SalesDate, C.Name

关于sql - 为什么这个查询不能正常工作？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22059901/