SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE A (
A1 nvarchar(40) NULL , --產品 
A2 nvarchar(40) NULL , --製程 
A3 nvarchar(40) NULL , --時間
A4 nvarchar(40) NULL); --狀態

INSERT INTO A (A1,A2,A3,A4)
VALUES 
('a123',N'裁切','2021/02/01','0'),
('a123',N'CNC','2021/02/02','0'),
('a123',N'打版','2021/02/03','0'),
('a123',N'入A庫','2021/02/04','1'),
('a123',N'出A庫','2021/02/05','0'),
('a456',N'K裁切','2021/03/01','0'),
('a456',N'KCNC','2021/03/02','0'),
('a456',N'K打版','2021/03/03','0'),
('a456',N'入A庫','2021/03/04','1'),
('a456',N'出A庫','2021/03/05','0');

SELECT *
FROM A;


A1
A2
A3
A4


a123
裁切
2021/02/01
0

a123
CNC
2021/02/02
0

a123
打版
2021/02/03
0

a123
入A庫
2021/02/04
1

a123
出A庫
2021/02/05
0

a456
K裁切
2021/03/01
0

a456
KCNC
2021/03/02
0

a456
K打版
2021/03/03
0

a456
入A庫
2021/03/04
1

a456
出A庫
2021/03/05
0


-- 以各產品的最後一筆'出A庫'查到各產品的'打版'
SELECT R.A1,R.A2,R.A3,R.A4
FROM (
SELECT Q.A1,Q.A2,Q.A3,Q.A4,Q.OrdNo3,
ROW_NUMBER() OVER (PARTITION BY Q.A1,Q.OrdNo3 ORDER BY Q.A1,Q.OrdNo3,Q.A3 DESC) AS OrdNo4
FROM (
SELECT Z.A1,Z.A2,Z.A3,Z.A4,P.OrdNo2,
ROW_NUMBER() OVER (PARTITION BY Z.A1,P.OrdNo2 ORDER BY Z.A1,P.OrdNo2,Z.A3 DESC) AS OrdNo3
FROM (
SELECT Y.A1,Y.A2,Y.A3,Y.A4,ROW_NUMBER() OVER (PARTITION BY Y.A1 ORDER BY Y.A1,Y.A3 DESC) AS OrdNo2
FROM A AS Y
LEFT JOIN (
SELECT V.A1,V.A2,V.A3,V.A4,V.OrdNo1
FROM (
SELECT X.A1,X.A2,X.A3,X.A4,ROW_NUMBER() OVER (PARTITION BY X.A1 ORDER BY X.A3 DESC) AS OrdNo1
FROM A AS X
WHERE X.A2=N'出A庫') AS V
WHERE V.OrdNo1=1
) AS U ON U.A1=Y.A1
WHERE Y.A2=N'入A庫' AND Y.A4='1'
AND Y.A1=U.A1 AND Y.A3<U.A3
) AS P 
LEFT JOIN A AS Z ON P.A1=Z.A1
WHERE Z.A3<P.A3
) AS Q
WHERE Q.OrdNo3=1
AND Q.A2<>N'出A庫' AND Q.A2<>N'入A庫'
) AS R
WHERE R.OrdNo4=1
ORDER BY R.A1


A1
A2
A3
A4


a123
打版
2021/02/03
0

a456
K打版
2021/03/03
0


DELETE A;
INSERT INTO A (A1,A2,A3,A4)
VALUES 
('a123',N'裁切','2021/02/01','0'),
('a123',N'CNC','2021/02/02','0'),
('a123',N'化學','2021/02/03','0'),
('a123',N'入A庫','2021/02/04','1'),
('a123',N'打版','2021/02/05','1'),
('a123',N'出A庫','2021/02/06','0'),
('a456',N'K裁切','2021/03/01','0'),
('a456',N'KCNC','2021/03/02','0'),
('a456',N'K化學','2021/03/03','0'),
('a456',N'入A庫','2021/03/04','1'),
('a456',N'K打版','2021/03/05','1'),
('a456',N'出A庫','2021/03/06','0');

SELECT *
FROM A;


A1
A2
A3
A4


a123
裁切
2021/02/01
0

a123
CNC
2021/02/02
0

a123
化學
2021/02/03
0

a123
入A庫
2021/02/04
1

a123
打版
2021/02/05
1

a123
出A庫
2021/02/06
0

a456
K裁切
2021/03/01
0

a456
KCNC
2021/03/02
0

a456
K化學
2021/03/03
0

a456
入A庫
2021/03/04
1

a456
K打版
2021/03/05
1

a456
出A庫
2021/03/06
0


-- 以各產品的最後一筆'出A庫'查到各產品的'化學'
SELECT R.A1,R.A2,R.A3,R.A4
FROM (
SELECT Q.A1,Q.A2,Q.A3,Q.A4,Q.OrdNo3,
ROW_NUMBER() OVER (PARTITION BY Q.A1,Q.OrdNo3 ORDER BY Q.A1,Q.OrdNo3,Q.A3 DESC) AS OrdNo4
FROM (
SELECT Z.A1,Z.A2,Z.A3,Z.A4,P.OrdNo2,
ROW_NUMBER() OVER (PARTITION BY Z.A1,P.OrdNo2 ORDER BY Z.A1,P.OrdNo2,Z.A3 DESC) AS OrdNo3
FROM (
SELECT Y.A1,Y.A2,Y.A3,Y.A4,ROW_NUMBER() OVER (PARTITION BY Y.A1 ORDER BY Y.A1,Y.A3 DESC) AS OrdNo2
FROM A AS Y
LEFT JOIN (
SELECT V.A1,V.A2,V.A3,V.A4,V.OrdNo1
FROM (
SELECT X.A1,X.A2,X.A3,X.A4,ROW_NUMBER() OVER (PARTITION BY X.A1 ORDER BY X.A3 DESC) AS OrdNo1
FROM A AS X
WHERE X.A2=N'出A庫') AS V
WHERE V.OrdNo1=1
) AS U ON U.A1=Y.A1
WHERE Y.A2=N'入A庫' AND Y.A4='1'
AND Y.A1=U.A1 AND Y.A3<U.A3
) AS P 
LEFT JOIN A AS Z ON P.A1=Z.A1
WHERE Z.A3<P.A3
) AS Q
WHERE Q.OrdNo3=1
AND Q.A2<>N'出A庫' AND Q.A2<>N'入A庫'
) AS R
WHERE R.OrdNo4=1
ORDER BY R.A1


A1
A2
A3
A4


a123
化學
2021/02/03
0

a456
K化學
2021/03/03
0


DELETE A;
INSERT INTO A (A1,A2,A3,A4)
VALUES 
('a123',N'裁切','2021/02/01','0'),
('a123',N'CNC','2021/02/02','0'),
('a123',N'化學','2021/02/03','0'),
('a123',N'雕刻','2021/02/04','0'),
('a123',N'入A庫','2021/02/05','1'),
('a123',N'出A庫','2021/02/06','0'),
('a123',N'入A庫','2021/02/07','1'),
('a123',N'出A庫','2021/02/08','0'),
('a456',N'K裁切','2021/03/01','0'),
('a456',N'KCNC','2021/03/02','0'),
('a456',N'K化學','2021/03/03','0'),
('a456',N'K雕刻','2021/03/04','0'),
('a456',N'入A庫','2021/03/05','1'),
('a456',N'出A庫','2021/03/06','0'),
('a456',N'入A庫','2021/03/07','1'),
('a456',N'出A庫','2021/03/08','0');

SELECT *
FROM A;


A1
A2
A3
A4


a123
裁切
2021/02/01
0

a123
CNC
2021/02/02
0

a123
化學
2021/02/03
0

a123
雕刻
2021/02/04
0

a123
入A庫
2021/02/05
1

a123
出A庫
2021/02/06
0

a123
入A庫
2021/02/07
1

a123
出A庫
2021/02/08
0

a456
K裁切
2021/03/01
0

a456
KCNC
2021/03/02
0

a456
K化學
2021/03/03
0

a456
K雕刻
2021/03/04
0

a456
入A庫
2021/03/05
1

a456
出A庫
2021/03/06
0

a456
入A庫
2021/03/07
1

a456
出A庫
2021/03/08
0


-- 以各產品的最後一筆'出A庫'查到各產品的'雕刻'
SELECT R.A1,R.A2,R.A3,R.A4
FROM (
SELECT Q.A1,Q.A2,Q.A3,Q.A4,Q.OrdNo3,
ROW_NUMBER() OVER (PARTITION BY Q.A1,Q.OrdNo3 ORDER BY Q.A1,Q.OrdNo3,Q.A3 DESC) AS OrdNo4
FROM (
SELECT Z.A1,Z.A2,Z.A3,Z.A4,P.OrdNo2,
ROW_NUMBER() OVER (PARTITION BY Z.A1,P.OrdNo2 ORDER BY Z.A1,P.OrdNo2,Z.A3 DESC) AS OrdNo3
FROM (
SELECT Y.A1,Y.A2,Y.A3,Y.A4,ROW_NUMBER() OVER (PARTITION BY Y.A1 ORDER BY Y.A1,Y.A3 DESC) AS OrdNo2
FROM A AS Y
LEFT JOIN (
SELECT V.A1,V.A2,V.A3,V.A4,V.OrdNo1
FROM (
SELECT X.A1,X.A2,X.A3,X.A4,ROW_NUMBER() OVER (PARTITION BY X.A1 ORDER BY X.A3 DESC) AS OrdNo1
FROM A AS X
WHERE X.A2=N'出A庫') AS V
WHERE V.OrdNo1=1
) AS U ON U.A1=Y.A1
WHERE Y.A2=N'入A庫' AND Y.A4='1'
AND Y.A1=U.A1 AND Y.A3<U.A3
) AS P 
LEFT JOIN A AS Z ON P.A1=Z.A1
WHERE Z.A3<P.A3
) AS Q
WHERE Q.OrdNo3=1
AND Q.A2<>N'出A庫' AND Q.A2<>N'入A庫'
) AS R
WHERE R.OrdNo4=1
ORDER BY R.A1


A1
A2
A3
A4


a123
雕刻
2021/02/04
0

a456
K雕刻
2021/03/04
0


DELETE A;
INSERT INTO A (A1,A2,A3,A4)
VALUES 
('a123',N'裁切','2021/02/01','0'),
('a123',N'CNC','2021/02/02','0'),
('a123',N'化學','2021/02/03','0'),
('a123',N'入A庫','2021/02/04','1'),
('a123',N'打版1','2021/02/05','1'),
('a123',N'出A庫','2021/02/06','0'),
('a123',N'打版11','2021/02/07','0'),
('a123',N'入A庫','2021/02/08','1'),
('a123',N'打版2','2021/02/09','1'),
('a123',N'出A庫','2021/02/10','0'),
('a456',N'K裁切','2021/03/01','0'),
('a456',N'KCNC','2021/03/02','0'),
('a456',N'K化學','2021/03/03','0'),
('a456',N'入A庫','2021/03/04','1'),
('a456',N'K打版1','2021/03/05','1'),
('a456',N'出A庫','2021/03/06','0'),
('a456',N'K打版11','2021/03/07','0'),
('a456',N'入A庫','2021/03/08','1'),
('a456',N'K打版2','2021/03/09','1'),
('a456',N'出A庫','2021/03/10','0');

SELECT *
FROM A;


A1
A2
A3
A4


a123
裁切
2021/02/01
0

a123
CNC
2021/02/02
0

a123
化學
2021/02/03
0

a123
入A庫
2021/02/04
1

a123
打版1
2021/02/05
1

a123
出A庫
2021/02/06
0

a123
打版11
2021/02/07
0

a123
入A庫
2021/02/08
1

a123
打版2
2021/02/09
1

a123
出A庫
2021/02/10
0

a456
K裁切
2021/03/01
0

a456
KCNC
2021/03/02
0

a456
K化學
2021/03/03
0

a456
入A庫
2021/03/04
1

a456
K打版1
2021/03/05
1

a456
出A庫
2021/03/06
0

a456
K打版11
2021/03/07
0

a456
入A庫
2021/03/08
1

a456
K打版2
2021/03/09
1

a456
出A庫
2021/03/10
0


-- 以各產品的最後一筆'出A庫'查到各產品的'打版11'
SELECT R.A1,R.A2,R.A3,R.A4
FROM (
SELECT Q.A1,Q.A2,Q.A3,Q.A4,Q.OrdNo3,
ROW_NUMBER() OVER (PARTITION BY Q.A1,Q.OrdNo3 ORDER BY Q.A1,Q.OrdNo3,Q.A3 DESC) AS OrdNo4
FROM (
SELECT Z.A1,Z.A2,Z.A3,Z.A4,P.OrdNo2,
ROW_NUMBER() OVER (PARTITION BY Z.A1,P.OrdNo2 ORDER BY Z.A1,P.OrdNo2,Z.A3 DESC) AS OrdNo3
FROM (
SELECT Y.A1,Y.A2,Y.A3,Y.A4,ROW_NUMBER() OVER (PARTITION BY Y.A1 ORDER BY Y.A1,Y.A3 DESC) AS OrdNo2
FROM A AS Y
LEFT JOIN (
SELECT V.A1,V.A2,V.A3,V.A4,V.OrdNo1
FROM (
SELECT X.A1,X.A2,X.A3,X.A4,ROW_NUMBER() OVER (PARTITION BY X.A1 ORDER BY X.A3 DESC) AS OrdNo1
FROM A AS X
WHERE X.A2=N'出A庫') AS V
WHERE V.OrdNo1=1
) AS U ON U.A1=Y.A1
WHERE Y.A2=N'入A庫' AND Y.A4='1'
AND Y.A1=U.A1 AND Y.A3<U.A3
) AS P 
LEFT JOIN A AS Z ON P.A1=Z.A1
WHERE Z.A3<P.A3
) AS Q
WHERE Q.OrdNo3=1
AND Q.A2<>N'出A庫' AND Q.A2<>N'入A庫'
) AS R
WHERE R.OrdNo4=1
ORDER BY R.A1


A1
A2
A3
A4


a123
打版11
2021/02/07
0

a456
K打版11
2021/03/07
0


DELETE A;
INSERT INTO A (A1,A2,A3,A4)
VALUES 
('a123',N'裁切','2021/02/01','0'),
('a123',N'CNC','2021/02/02','0'),
('a123',N'化學','2021/02/03','0'),
('a123',N'雕刻','2021/02/04','0'),
('a123',N'入A庫','2021/02/05','1'),
('a123',N'mm','2021/02/06','0'),
('a123',N'出A庫','2021/02/07','0'),
('a123',N'dd','2021/02/08','0'),
('a123',N'入A庫','2021/02/09','1'),
('a123',N'PP','2021/02/10','0'),
('a123',N'出A庫','2021/02/11','0'),
('a123',N'入A庫','2021/02/12','1'),
('a123',N'出A庫','2021/02/13','0'),
('a456',N'裁切','2021/03/01','0'),
('a456',N'CNC','2021/03/02','0'),
('a456',N'化學','2021/03/03','0'),
('a456',N'雕刻','2021/03/04','0'),
('a456',N'入A庫','2021/03/05','1'),
('a456',N'mm','2021/03/06','0'),
('a456',N'出A庫','2021/03/07','0'),
('a456',N'Kdd','2021/03/08','0'),
('a456',N'入A庫','2021/03/09','1'),
('a456',N'PP','2021/03/10','0'),
('a456',N'出A庫','2021/03/11','0'),
('a456',N'入A庫','2021/03/12','1'),
('a456',N'出A庫','2021/03/13','0'),
('a789',N'裁切','2021/04/01','0'),
('a789',N'CNC','2021/04/02','0'),
('a789',N'化學','2021/04/03','0'),
('a789',N'雕刻','2021/04/04','0'),
('a789',N'入A庫','2021/04/05','1'),
('a789',N'mm','2021/04/06','0'),
('a789',N'出A庫','2021/04/07','0'),
('a789',N'Mdd','2021/04/08','0'),
('a789',N'入A庫','2021/04/09','1'),
('a789',N'PP','2021/04/10','0'),
('a789',N'出A庫','2021/04/11','0'),
('a789',N'入A庫','2021/04/12','1'),
('a789',N'出A庫','2021/04/13','0');

SELECT *
FROM A;


A1
A2
A3
A4


a123
裁切
2021/02/01
0

a123
CNC
2021/02/02
0

a123
化學
2021/02/03
0

a123
雕刻
2021/02/04
0

a123
入A庫
2021/02/05
1

a123
mm
2021/02/06
0

a123
出A庫
2021/02/07
0

a123
dd
2021/02/08
0

a123
入A庫
2021/02/09
1

a123
PP
2021/02/10
0

a123
出A庫
2021/02/11
0

a123
入A庫
2021/02/12
1

a123
出A庫
2021/02/13
0

a456
裁切
2021/03/01
0

a456
CNC
2021/03/02
0

a456
化學
2021/03/03
0

a456
雕刻
2021/03/04
0

a456
入A庫
2021/03/05
1

a456
mm
2021/03/06
0

a456
出A庫
2021/03/07
0

a456
Kdd
2021/03/08
0

a456
入A庫
2021/03/09
1

a456
PP
2021/03/10
0

a456
出A庫
2021/03/11
0

a456
入A庫
2021/03/12
1

a456
出A庫
2021/03/13
0

a789
裁切
2021/04/01
0

a789
CNC
2021/04/02
0

a789
化學
2021/04/03
0

a789
雕刻
2021/04/04
0

a789
入A庫
2021/04/05
1

a789
mm
2021/04/06
0

a789
出A庫
2021/04/07
0

a789
Mdd
2021/04/08
0

a789
入A庫
2021/04/09
1

a789
PP
2021/04/10
0

a789
出A庫
2021/04/11
0

a789
入A庫
2021/04/12
1

a789
出A庫
2021/04/13
0


-- 以各產品的最後一筆'出A庫'查到各產品的'dd'
SELECT R.A1,R.A2,R.A3,R.A4
FROM (
SELECT Q.A1,Q.A2,Q.A3,Q.A4,Q.OrdNo3,
ROW_NUMBER() OVER (PARTITION BY Q.A1,Q.OrdNo3 ORDER BY Q.A1,Q.OrdNo3,Q.A3 DESC) AS OrdNo4
FROM (
SELECT Z.A1,Z.A2,Z.A3,Z.A4,P.OrdNo2,
ROW_NUMBER() OVER (PARTITION BY Z.A1,P.OrdNo2 ORDER BY Z.A1,P.OrdNo2,Z.A3 DESC) AS OrdNo3
FROM (
SELECT Y.A1,Y.A2,Y.A3,Y.A4,ROW_NUMBER() OVER (PARTITION BY Y.A1 ORDER BY Y.A1,Y.A3 DESC) AS OrdNo2
FROM A AS Y
LEFT JOIN (
SELECT V.A1,V.A2,V.A3,V.A4,V.OrdNo1
FROM (
SELECT X.A1,X.A2,X.A3,X.A4,ROW_NUMBER() OVER (PARTITION BY X.A1 ORDER BY X.A3 DESC) AS OrdNo1
FROM A AS X
WHERE X.A2=N'出A庫') AS V
WHERE V.OrdNo1=1
) AS U ON U.A1=Y.A1
WHERE Y.A2=N'入A庫' AND Y.A4='1'
AND Y.A1=U.A1 AND Y.A3<U.A3
) AS P 
LEFT JOIN A AS Z ON P.A1=Z.A1
WHERE Z.A3<P.A3
) AS Q
WHERE Q.OrdNo3=1
AND Q.A2<>N'出A庫' AND Q.A2<>N'入A庫'
) AS R
WHERE R.OrdNo4=1
ORDER BY R.A1


A1
A2
A3
A4


a123
dd
2021/02/08
0

a456
Kdd
2021/03/08
0

a789
Mdd
2021/04/08
0
A1	A2	A3	A4
a123	裁切	2021/02/01	0
a123	CNC	2021/02/02	0
a123	打版	2021/02/03	0
a123	入A庫	2021/02/04	1
a123	出A庫	2021/02/05	0
a456	K裁切	2021/03/01	0
a456	KCNC	2021/03/02	0
a456	K打版	2021/03/03	0
a456	入A庫	2021/03/04	1
a456	出A庫	2021/03/05	0