我有兩個表，我必須通過在具有先前值的 emphistory 表中查找來更新 emp 表中的地址欄位，即員工約翰的美國
表emp
EId ename sal Address AccountId
-------------------------------
101 John 100 U X12
102 Peter 500 Null X13
表記號
emphisid EId AccountId Address Date (use row_number to find the second record for that eid and accountid)
-----------------------------------------------------
1 101 X12 U 11-01-2020 09:45:00
2 102 X13 Null 11-01-2020 09:46:00
3. 101 X12 USA 11-01-2020 09:30:00
我必須使用帳戶 ID 和 eid 加入表。
uj5u.com熱心網友回復：
這適用于 Postgresql 和 Sql Server
UPDATE emp
SET Address = hist.Address
FROM (
SELECT h.EId, h.AccountId, h.Address
, ROW_NUMBER() OVER (PARTITION BY h.EId, h.AccountId
ORDER BY h.Date DESC) AS Rn
FROM emphistory h
JOIN emp e
ON e.EId = h.EId
AND e.AccountId = h.AccountId
WHERE h.Address IS NOT NULL
) hist
WHERE emp.EId = hist.EId
AND hist.Rn = 2
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/362595.html
標籤：sql