iT邦幫忙

0

SQL 如何做到連續搜尋 / 繼續搜尋 / 迴圈

各位大大好
問題情境如下:
以下為表格ProductHistory 的範例資料
備註說明:
1.入庫時狀態會變為1,出庫變回0
2.入庫可以選擇出庫或是繼續走製程
3.時間已經照順序排序好了
4.製程名稱為範例

搜尋條件為:
必須在出庫後,可以查到最新的入庫前的製程
如果入庫前面的製程是出庫,就是繼續找上一個入庫

下表要查到打版

產品       製程         時間              狀態
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

下表要查到化學

產品       製程         時間              狀態
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

下表要查到雕刻

產品       製程         時間              狀態
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

下表要查到打版11

產品       製程         時間              狀態
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

也就是說同樣是查到最新一筆的入A庫之前的製程,條件會遇到四種情境
1.入A庫出A庫
2.入A庫 => 製程 => 出A庫
3.入A庫出A庫入A庫出A庫
4.入A庫出A庫=> 製程 =>入A庫出A庫

如果是1跟2、4還好解決,但3變成要用迴圈跑的概念

還請大大幫忙解惑

感恩!!!

1 個回答

1
rogeryao
iT邦大師 1 級 ‧ 2021-02-05 21:19:44
最佳解答

更新 :

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'雕刻','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');
-- 以各產品的最後一筆'出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

Demo

看更多先前的回應...收起先前的回應...

感謝大大的回答
不好意思說明沒有明確,已經更新
搜尋條件為:
必須在出庫後,可以查到最新的入庫前的製程
如果入庫前面的製程是出庫,就是繼續找上一個入庫

也就是如果是以下情境
下表要查到打版11

產品       製程         時間              狀態
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
rogeryao iT邦大師 1 級 ‧ 2021-02-05 22:05:11 檢舉

哈,多了"下表要查到打版11"....情境

rogeryao iT邦大師 1 級 ‧ 2021-02-05 22:37:19 檢舉

程式碼已更新在上方了

了解了~感恩
倒著排設條件就可以了~ 想太複雜想去找不到繼續找的那種解法

謝謝

我要發表回答

立即登入回答