我想要一個簡單的查詢，讓我每天都有庫存細節。
我寫了這段代碼：
SELECT
TRNDATE, I_NAME,
SUM(AMOUNT) AS OPENING, SUM(PURCHASE) AS PURCHASE,
SUM(SALE) AS SALE, SUM(AMOUNT) SUM(PURCHASE) - SUM(SALE) AS STOCK
FROM
(SELECT
TRNDATE, I_NAME, AMOUNT AS OPENING, 0 AS PURCHASE,
0 AS SALE, 0 AS STOCK
FROM DBO.ITEMMAST
UNION ALL
SELECT
TRNDATE, I_NAME, 0 AS OPENING, AMOUNT AS PURCHASE,
0 AS SALE, 0 AS STOCK
FROM DBO.PURCHASE
UNION ALL
SELECT
TRNDATE, I_NAME, 0 AS OPENING, 0 AS PURCHASE,
AMOUNT AS SALE,0 AS STOCK
FROM DBO.SALE) MAIN
GROUP BY
TRNDATE, I_NAME
輸出是
| 物品 | 日期 | 開幕 | 購買 | 銷售 | 庫存 |
|---|---|---|---|---|---|
| 金子 | 2022 年 10 月 1 日 | 10 | 0 | 0 | 10 |
| 金子 | 2022 年 11 月 1 日 | 0 | 5 | 2 | 3 |
| 金子 | 2022 年 12 月 1 日 | 0 | 0 | 2 | -2 |
結果僅回傳相同的行摘要
我想變成這樣
| 物品 | 日期 | 開幕 | 購買 | 銷售 | 庫存 |
|---|---|---|---|---|---|
| 金子 | 2022 年 10 月 1 日 | 10 | 0 | 0 | 10 |
| 金子 | 2022 年 11 月 1 日 | 0 | 5 | 2 | 13 |
| 金子 | 2022 年 12 月 1 日 | 0 | 0 | 2 | 11 |
我需要逐行總結。
uj5u.com熱心網友回復：
我懷疑這是您所追求的，但是，無法測驗。你想要的是一個累積的SUM，它是在OVER子句末尾的那個。因此，您需要使用另一個派生表。
我更喜歡在子查詢上使用 CTE，因為它們可以通過查詢重用，所以我將您的查詢更改為使用這些并最終得到以下結果：
WITH Items AS(
SELECT TRNDATE,
I_NAME,
AMOUNT AS OPENING,
0 AS PURCHASE,
0 AS SALE,
0 AS STOCK
FROM dbo.ITEMMAST
UNION ALL
SELECT TRNDATE,
I_NAME,
0 AS OPENING,
AMOUNT AS PURCHASE,
0 AS SALE,
0 AS STOCK
FROM dbo.PURCHASE
UNION ALL
SELECT TRNDATE,
I_NAME,
0 AS OPENING,
0 AS PURCHASE,
AMOUNT AS SALE,
0 AS STOCK
FROM dbo.SALE),
Aggregrates AS(
SELECT TRNDATE,
I_NAME,
SUM(OPENING) AS OPENING, --Note you had SUM(AMOUNT) in your original attempt
SUM(PURCHASE) AS PURCHASE,
SUM(SALE) AS SALE
FROM Items
GROUP BY TRNDATE,
I_NAME)
SELECT TRNDATE,
I_NAME,
OPENING,
PURCHASE,
SALE,
SUM(OPENING PURCHASE - SALE) OVER (PARTITION BY I_NAME ORDER BY TRNDATE
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS STOCK
FROM Aggregrates;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/407384.html
標籤：
上一篇：無法安裝引導程式
下一篇：管理貨幣列的百分比