日期 訂單號 金額
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
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/46273.html
標籤：非技術版