以上是我想輸出的資料
目前我的功力只會進出SUM而已
卡在不知道"某一個欄位"要取前一天的值怎麼取,
我目前只能想到分開進資料庫撈資料而已QQ <- 但這邊我又卡在 假設前一天沒資料我要如何取的前一個工作天的內容 (因為有工作就會存DB)
select a.Id ,
SUM(b.in) ,
SUM(b.out),
a.stock //前一天的資料 <- 7/30 的庫存
from A_Table a
left join B_Table b on a.id = b.id
where a.id = 1 and (a.createDate between '20210801' and '20210805')
group by a.id
玩一下...
CREATE TABLE A_TABLE(
CREATEDATE DATE,
ID VARCHAR(20),
STOCK INT);
INSERT INTO A_TABLE
VALUES
('20210730','1',10),
('20210801','1',55),
('20210802','1',75),
('20210803','1',20),
('20210804','1',5),
('20210805','1',0),
--
('20210725','2',110),
('20210801','2',255),
('20210802','2',375),
--
('20210720','3',500);
CREATE TABLE B_TABLE(
ID VARCHAR(20),
QTYIN INT,
QTYOUT INT,
CREATEDATE DATE);
INSERT INTO B_TABLE
VALUES
('1',0,0,'20210730'),
('1',45,0,'20210801'),
('1',20,0,'20210802'),
('1',0,55,'20210803'),
('1',0,15,'20210804'),
('1',0,0,'20210805'),
--
('2',0,0,'20210725'),
('2',145,0,'20210801'),
('2',120,0,'20210802');
SELECT X.ID,X.CREATEDATE AS 最後異動日期,
COALESCE(X.STOCK,0) AS 最後異動日期庫存,
COALESCE(Y.IN,0) AS IN,
COALESCE(Y.OUT,0) AS OUT,
COALESCE(X.STOCK,0)+COALESCE(Y.IN,0)-COALESCE(Y.OUT,0) AS 庫存
FROM (
SELECT DISTINCT ON (ID) ID,CREATEDATE,STOCK
FROM A_TABLE
WHERE CREATEDATE<TO_DATE('20210801','YYYYMMDD')
ORDER BY ID, CREATEDATE DESC) AS X
LEFT JOIN (
SELECT ID,SUM(QTYIN) AS IN,SUM(QTYOUT) AS OUT
FROM B_TABLE
WHERE CREATEDATE>=TO_DATE('20210801','YYYYMMDD') AND CREATEDATE<=TO_DATE('20210805','YYYYMMDD')
GROUP BY ID
) AS Y ON Y.ID=X.ID
ORDER BY X.ID