declare @tbl as table
(
ItemId int,
SOQty int,
DIQty int ,
IssueQty int,
BalanceQty int,
CreateDate datetime,
StockQty int
)
insert into @tbl
values (2, 5, 5, 1, 4, '2021-12-16 19:28:05.200', 80),
(1, 10, 10, 0,10, '2021-12-16 19:28:32.200', 10),
(1, 15, 10, 10,5, '2021-12-16 19:28:34.200', 10),
(1, 8, 5, 2, 3, '2021-12-16 19:28:35.200', 10)
有 2 個UPDATE陳述句都在給定條件下更新
update x
set x.StockQty = tx.StockQty
from @tbl x
join
(select *
from
(select
*,
row_number() over (partition by itemid order by CreateDate) as RowNo
from @tbl) as t
where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate
update x
set x.StockQty = 0
from @tbl x
join
(select *
from
(select
*,
row_number() over (partition by itemid order by CreateDate) as RowNo
from @tbl) as t
where t.RowNo != 1) as tx on tx.CreateDate = x.CreateDate
如果 RowNo=1 then x.StockQty = tx.StockQty else x.StockQty = 0
update x set x.StockQty = case when tx.RowNo = 1 then x.StockQty = tx.StockQty else x.StockQty end from @tbl x
join
(select * from
(
select *,ROW_NUMBER()over(partition by itemid order by CreateDate) as RowNo from @tbl
)as t where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate
我想要一個更新陳述句，到目前為止我已經嘗試過
uj5u.com熱心網友回復：
看起來你把它復雜化了，你可以直接更新派生表
UPDATE t
SET StockQty = 0
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY CreateDate) as RowNo
FROM @tbl
) AS t
WHERE t.RowNo > 1;
資料庫<>小提琴
uj5u.com熱心網友回復：
實際上你的第一個更新查詢什么都不做，但正如你所希望的，我認為下面的這個查詢適合你的場景：
update x
set x.StockQty = CASE WHEN ISNULL(tx.RowNo,0) = 1 THEN tx.StockQty ELSE 0 END
from @tbl x
left join
(select *
from
(select
*,
row_number() over (partition by itemid order by CreateDate) as RowNo
from @tbl) as t
where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate
left join
(select *
from
(select
*,
row_number() over (partition by itemid order by CreateDate) as RowNo
from @tbl) as t
where t.RowNo != 1) as tx2 on tx2.CreateDate = x.CreateDate
uj5u.com熱心網友回復：
您可以使用 CTE 并更新 CTE：
With rankedResults
As (
Select *
, rn = row_number() over (partition by t.itemid order by t.CreateDate)
From @tbl t
)
Update rankedResults
Set StockQty = 0
Where rn > 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/384647.html
標籤：sql sql-server