使用 CROSS JOIN 的 SQL 运行总计

Question

需要根据日期和另一列对表进行总计。假设我有一个名为 Sales 的表：

Day      Client

1        Smith, J
3        Johnson, B
6        Fuller, A
7        Smith, J
8        Johnson, B
9        Lee, M

我运行以下查询：

SELECT a.Day, a.Client, SUM(1) AS RunningTotal
FROM Sales a CROSS JOIN Sales b
WHERE (b.Day <= a.Day) 
GROUP BY a.Day, a.Client
ORDER BY a.Day

这给出了以下结果：

Day Client       RunningTotal

1   Smith, J     1
3   Johnson, B   2
6   Fuller, A    3
7   Smith, J     4
8   Johnson, B   5
9   Lee, M       6

但这只正确了一半。我希望运行总计基于当天和客户。我想要的表如下所示：

Day Client       RunningTotal

1   Smith, J     1
3   Johnson, B   1
6   Fuller, A    1
7   Smith, J     2
8   Johnson, B   2
9   Lee, M       1

Answer 1

您不需要将 Client 添加到 JOIN 中吗？

SELECT a.Day, a.Client, SUM(1) AS RunningTotal
FROM Sales a 
JOIN Sales b ON b.Day <= a.Day and a.Client = b.Client 
GROUP BY a.Day, a.Client
ORDER BY a.Day

顺便说一句：这种计算运行总计的方法仅真正适合小型输入集。所需的工作呈指数级增长。

Answer 2

干得好。

Select a.Day, a.Client, SUM(1) AS RunningTotal
From dbo.Sales a, dbo.Sales b
Where b.Day <= a.Day And a.Client = b.Client
Group By a.Day, a.Client
Order By a.Day