【SQL】請問如何取得最新一筆修改日期的商品資料,並且更新價格至另一章表

sql exists update

partylin 2021-01-20 17:01:43 ‧ 2228 瀏覽

請問目前有 A表=商品資料 B表=價格資料(歷屆商品價格更改資料)
要將B表最新一筆修改過的價格,更新至A表上
報錯訊息
【當子查詢並未由 EXISTS 導入時，只能在選取清單中指定一個運算式。】
請問該怎解呢?

--取得最新資料修改日期的資料

with temp as(
SELECT *, ROW_NUMBER() over(order by 修改日期,商品價格) as rnk  --以價格最高
FROM 價格資料
)
  
--以價格資料  更新至 商品資料

UPDATE  A SET 
A.會員價 = B.會員價格,
A.牌價   =B.牌價

FROM 商品資料 AS  A  ,
(select * from temp where rnk IN (SELECT Max(rnk),max(商品價格) FROM temp  GROUP BY 商品條碼) )
AS B  
WHERE A.商品條碼 = B.商品條碼

w4560000 iT邦研究生 5 級 ‧ 2021-01-20 17:42:40 檢舉

可以附上 Table schema Create script 和 insert 測試資料的script嗎?

石頭 iT邦高手 1 級 ‧ 2021-01-20 17:43:48 檢舉

你可以嘗試使用
Row_number() with window function

partylin iT邦新手 5 級 ‧ 2021-01-20 18:32:10 檢舉

感謝,還沒來得及弄好 insert script已被解答完成

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 8 級 ‧ 2021-01-20 18:09:17

最佳解答

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);

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

回應 2
分享
檢舉

partylin iT邦新手 5 級 ‧ 2021-01-20 18:30:52 檢舉

感謝大師協助...
還沒來得及回復樓上的 insert 測試資料的script就搞定了

rogeryao iT邦超人 8 級 ‧ 2021-01-20 19:47:35 檢舉

版主提到:
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

登入發表回應