每周汇总，即使是空行

Question

我想按周总结sales.quantity的总和，即使没有销售，也要显示周数。

我已经在其中设置了带有1-54的周表，以使用外部联接强制所有周数都通过，但是它不起作用。它错过了没有销售的几周。

我的查询是：

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks LEFT OUTER JOIN 
     sales ON Weeks.WeekNum = DATEPART(week, sales.transDate)
WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
GROUP BY Weeks.WeekNum

任何帮助都会得到很大的帮助……这可能是我做的愚蠢的事情！

Answer 1

where子句WHERE (sales.transDate BETWEEN @fromDate AND @toDate)将删除所有没有销售的星期。您可能需要执行子查询以拉动交易，然后将其加入周表。

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks LEFT OUTER JOIN 
 (
    SELECT *
    FROM sales 
    WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
 ) sales
    ON Weeks.WeekNum = DATEPART(week, sales.transDate)
GROUP BY Weeks.WeekNum