【SQL】B表更新至A表,並取得指定最新一筆資料

by zhi hao
CREATE TABLE [商品資料] (  [商品條碼] Nvarchar(40) NULL ,  [會員價] INT NULL ,  [牌價] INT NULL);  INSERT [商品資料] ([商品條碼], [會員價], [牌價])  VALUES ('123',1000,1010), ('456',2000,2010);

建立B表資料

CREATE TABLE [價格資料] ( [商品條碼] Nvarchar(40) NULL , [商品價格] INT NULL , [會員價格] INT NULL , [牌價] INT NULL , [修改日期] Nvarchar(40) NULL); INSERT [價格資料] ([商品條碼],[商品價格],[會員價格],[牌價],[修改日期]) VALUES ('123',1100,950,1010,'20200101'), ('123',3100,2950,3010,'20200201'), ('456',5100,4950,5010,'20200301'), ('456',7100,6950,7010,'20200401');
UPDATE A SET A.[會員價]=C.[會員價格], A.[牌價]=C.牌價 FROM [商品資料] AS A INNER JOIN [價格資料] AS C ON A.[商品條碼]=C.[商品條碼] INNER JOIN ( SELECT B.[商品條碼],MAX(B.[修改日期]) AS [修改日期] FROM [價格資料] AS B GROUP BY B.[商品條碼] ) AS D ON C.[商品條碼]=D.[商品條碼] AND C.[修改日期]=D.[修改日期];

Demo 範例

 

1.B表最新一筆修改過的價格
2.over(order by 修改日期,商品價格) as rnk –以價格最高

推估:同一[商品條碼]的[價格資料]在同一天會有多筆資料,要取”價格最高”為基礎更新[商品資料]的[會員價]及[牌價]

CREATE TABLE [商品資料] ( [商品條碼] Nvarchar(40) NULL , [會員價] INT NULL , [牌價] INT NULL); INSERT [商品資料] ([商品條碼], [會員價], [牌價]) VALUES ('123',1000,1010), ('456',2000,2010);

加入[修改序號]欄位

CREATE TABLE [價格資料] ( [商品條碼] Nvarchar(40) NULL , [商品價格] INT NULL , [會員價格] INT NULL , [牌價] INT NULL , [修改日期] Nvarchar(40) NULL, [修改序號] INT NULL); INSERT [價格資料] ([商品條碼],[商品價格],[會員價格],[牌價],[修改日期],[修改序號]) VALUES ('123',1200,950,1010,'20200101',1), ('123',1100,900,1020,'20200101',2), ('123',3200,2950,3010,'20200201',1), ('123',3100,2900,3020,'20200201',2), ('456',5200,4950,5010,'20200301',1), ('456',5100,4900,5020,'20200301',2), ('456',7100,6950,7010,'20200401',1), ('456',7200,6900,7020,'20200401',2);
UPDATE A SET A.[會員價]=C.[會員價格], A.[牌價]=C.牌價 FROM [商品資料] AS A INNER JOIN [價格資料] AS C ON A.[商品條碼]=C.[商品條碼] INNER JOIN ( SELECT X.[商品條碼],X.[修改日期],X.[商品價格] FROM (SELECT B.[商品條碼],B.[修改日期],B.[商品價格], Row_Number() OVER (PARTITION BY B.[商品條碼] ORDER BY B.[修改日期] DESC,B.[商品價格] DESC) AS ROWNUM FROM [價格資料] AS B) AS X WHERE X.[ROWNUM]=1 ) AS D ON C.[商品條碼]=D.[商品條碼] AND C.[修改日期]=D.[修改日期] AND C.[商品價格]=D.[商品價格]

DEMO

相關文章

本網站放置cookies,務求為您提供最佳的網上瀏覽體驗。 接受 閱讀更多