sql 怎么求整单合计数

[leizhuifee]

日期 订单号 金额

2020-03-30 10001 1000
2020-03-30 10001 2000
2020-03-30 10001 3000
2020-03-30 10002 2000


要的效果时在最后加一类金额合计


日期 订单号 金额 本单合计

2020-03-30 10001 1000 6000
2020-03-30 10001 2000 0
2020-03-30 10001 3000 0
2020-03-30 10002 2000 2000

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(DT DATE,
 ORDER_NO VARCHAR(10),
 AMOUNT INT)

INSERT INTO #T
SELECT '2020-03-30','10001',1000 UNION ALL
SELECT '2020-03-30','10001',2000 UNION ALL
SELECT '2020-03-30','10001',3000 UNION ALL
SELECT '2020-03-30','10002',2000

SELECT DT,ORDER_NO,AMOUNT,
CASE WHEN SEQ=1 THEN TOTAL ELSE 0 END AS TOTAL
FROM 
(SELECT *,SUM(AMOUNT) OVER (PARTITION BY ORDER_NO,DT) AS TOTAL,ROW_NUMBER() OVER (PARTITION BY ORDER_NO,DT ORDER BY (SELECT 1)) AS SEQ
FROM #T) AS A