如果臨時表中尚未使用該EquipmentMasterId列，我撰寫此查詢的目的是更新#TempRequestItemEquipment表中的臨時表列。但它不起作用，它正在更新臨時表的所有行中的相同設備主 ID。EquipmentMasterEquipmentMasterId
update #TempRequestItemEquipment
set EquipmentMasterId=
(
select top 1 em.Id from EquipmentMaster em
left join #TempRequestItemEquipment t on em.Id=t.EquipmentMasterId
where t.EquipmentMasterId is null
)
因此，基本上它無法在更新表時動態檢測表中的更改。
那我怎樣才能做到這一點？
基本上我想要的是 distinctEquipmentMasterId應該填充在我的臨時表中。
使用游標和回圈是可能的，但我不想那樣做，那么有沒有辦法在一個查詢中實作它？
uj5u.com熱心網友回復：
好的，我終于想出了with像這樣使用的查詢并且它有效。我正在使用ROW_NUMBER隨機連接兩個表
;with requestItems as
(
select ROW_NUMBER() over(order by ri.Id) as RowNumber, ri.*
from RequestItem ri
join Request r on ri.RequestId=r.Id
where r.RequestNumber=@requestNumber
),
equipmentMasters as
(
select ROW_NUMBER() over(order by Id) as RowNumber,*
from EquipmentMaster
),
final as
(
select
em.Id AS EquipmentMasterId,
ri.Id AS RequestItemId,
NULL AS ValueDomainId,
NULL AS ParentEquipmentId,
1 AS IsRootParent,
0 AS DeletionIndicator,
NULL AS BoxID,
1 AS IsActive
from requestItems ri
join equipmentMasters em on ri.RowNumber=em.RowNumber
)
select * from final
uj5u.com熱心網友回復：
看起來你想要這個
update t
from #TempRequestItemEquipment t
left join #TempRequestItemEquipment t on em.Id = t.EquipmentMasterId ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346763.html
標籤：sql sql-server 查询语句