如何在我的ResultSet中叠加从未发生过的事务行?

Question

下面是我的查询结果…

DROP TABLE IF EXISTS #test_grouping3;
DROP TABLE IF EXISTS #first_day_of_month;
CREATE TABLE #test_grouping3 (
Customer VARCHAR(1),
STOTALAMOUNT DECIMAL (10,2),
UPOSTDATE DATETIME
)
INSERT INTO #test_grouping3
VALUES ('A', 50.0, '2020-01-01')
INSERT INTO #test_grouping3
VALUES ('B', 10.0, '2020-02-01')
INSERT INTO #test_grouping3
VALUES ('A', 25.0, '2020-03-01')
CREATE TABLE #first_day_of_month (
FirstDateOfMonth DATETIME
)
INSERT INTO #first_day_of_month
VALUES ('2020-01-01')
INSERT INTO #first_day_of_month
VALUES ('2020-02-01')
INSERT INTO #first_day_of_month
VALUES ('2020-03-01')
INSERT INTO #first_day_of_month
VALUES ('2020-04-01')
SELECT
a.Customer
,dt.FirstDateofMonth AS The_UPOSTDATE
,ISNULL(SUM(a.STOTALAMOUNT), 0) AS TransTotal
FROM #test_grouping3 a
FULL JOIN (SELECT
FirstDateOfMonth
FROM #first_day_of_month
WHERE 1 = 1) dt
ON a.UPOSTDATE = dt.FirstDateofMonth
WHERE 1 = 1
GROUP BY Customer
,dt.FirstDateofMonth
ORDER BY Customer
, dt.FirstDateofMonth

结果

tbody> <<tr>B

Customer	The_UPOSTDATE	TransTotal
空	2020-04-01	0.00
	2020-01-01	50.00
	2020-03-01	25.00
	2020-02-01	10.00

每个月的每个客户，您可以看到下面的代码:客户金额从2020-02-01到2020-04-01的SUM，不知道，可能需要使用union

SELECT
dt.Customer
,dt.FirstDateofMonth AS The_UPOSTDATE
,ISNULL(SUM(a.STOTALAMOUNT), 0) AS TransTotal
FROM #test_grouping3 a
FULL JOIN 
( -- cross join customer and month to create full customer and month pair.
SELECT  mon.FirstDateOfMonth, c.Customer
FROM #first_day_of_month mon, (select distinct customer from #test_grouping3) c
WHERE 1 = 1) dt ON a.UPOSTDATE = dt.FirstDateofMonth and a.Customer = dt.Customer
WHERE 1 = 1
GROUP BY dt.Customer
,dt.FirstDateofMonth
ORDER BY Customer
, dt.FirstDateofMonth

我猜你是想每月合计一下，那么你可以使用:

SELECT  
mon.FirstDateOfMonth
, c.Customer
,(select isnull(sum(STOTALAMOUNT),0) from #test_grouping3 t where t.UPOSTDATE <= mon.FirstDateOfMonth and t.Customer = c.Customer) AS TransTotal
FROM #first_day_of_month mon
, (select distinct customer from #test_grouping3) c