对于所有列值的组合，仅更新具有最高自定义优先级的行

Question

我有一个下表(输入表(，需要根据orderid、merchantId和uniqueId的所有组合的状态优先级将is_latest更新为true优先级为：

RETURNED  - 1 
CANCELLED - 2
SHIPPED -   3
ORDDERED -  4

输入

OrderId |MerchantId | uniqueId | status  | is_latest
O1        M1           U1        ORDERED   F
O2        M2           U2        ORDERED   F
O1        M1           U1        SHIPPED   F
O2        M2           U2        SHIPPED   F
O2        M2           U2        CANCELLED F
O3        M3           U3        ORDERED   F

结果应该是：

OrderId |MerchantId | uniqueId | status   | is_latest
O1       M1           U1        ORDERED     F
O2       M2           U2        ORDERED     F
O1       M1           U1        SHIPPED     T
O2       M2           U2        SHIPPED     F
O2       M2           U2        CANCELLED   T
O3       M3           U3        ORDERED     T

我看到了一些例子，但没有一个是用于更新列的所有组合

提前感谢

----------我尝试了以下查询------------------

WITH cte2 AS
(
SELECT OrderId , merchantId , uniqueId,
ROW_NUMBER() OVER (PARTITION BY OrderId , merchantId , iniqueId ORDER BY
CASE order_status 
WHEN 'ORDERED' THEN 4
WHEN 'SHIPPED' THEN 3
WHEN 'CANCELLED' THEN 2
WHEN 'RETURNED' THEN 1
ELSE 5
END 
) AS rn
FROM table1
) 
Update rdyip 
SET rdyip.is_latest = 'T'
FROM table rdyip
inner JOIN 
cte2 
on 
rdyip.OrderId = cte2.OrderId and 
rdyip.MerchantId = cte2.MerchantId and 
rdyip.uniqueId = cte2.uniqueId 
where cte2.rn=1

它将所有行更新为is_latest='T'

Answer 1

首先，您不需要JOIN。SQL Server支持可更新的CTE。

其次，您似乎只想要每个商家最近的一行。您的PARTITION BY的列太多。

因此：

WITH toupdate as (
SELECT t1.*,
ROW_NUMBER() OVER (PARTITION BY merchantId
ORDER BY (CASE order_status 
WHEN 'ORDERED' THEN 4
WHEN 'SHIPPED' THEN 3
WHEN 'CANCELLED' THEN 2
WHEN 'RETURNED' THEN 1
ELSE 5
END) 
) AS seqnum
FROM table1
) 
UPDATE toupdate 
SET is_latest = 'T'
WHERE seqnum = 1;

如果您想同时将其他行设置为'F'，可以使用：

UPDATE toupdate 
SET is_latest = (CASE WHEN seqnum = 1 THEN 'T' ELSE 'F' END);

Answer 2

您可以使用分析函数，类似于以下内容：

SELECT
a.OrderId
,a.MerchantId
,a.uniqueId
,a.status
,IIF(ROW_NUMBER() OVER (PARTITION BY a.OrderId, a.MerchantId, a.uniqueId ORDER BY n.ordinal) = 1, 'T', 'F')
FROM (VALUES
('O1', 'M1', 'U1', 'ORDERED', 'F')
, ('O2', 'M2', 'U2', 'ORDERED', 'F')
, ('O1', 'M1', 'U1', 'SHIPPED', 'F')
, ('O2', 'M2', 'U2', 'SHIPPED', 'F')
, ('O2', 'M2', 'U2', 'CANCELLED', 'F')
, ('O3', 'M3', 'U3', 'ORDERED', 'F')
) a (OrderId, MerchantId, uniqueId, status, is_latest)
INNER JOIN (VALUES
('RETURNED', 1)
, ('CANCELLED', 2)
, ('SHIPPED', 3)
, ('ORDERED', 4)
) n (status, ordinal)
ON n.status = a.status

我只是将可能的状态连接起来，以获得每个状态的序数值，然后使用ROW_NUMBER在每个唯一的组合中按序数对行进行排序。

最低的序数将是第1行，因此如果row_number为1，则为lates，否则为lates。

Answer 3

只需要在where子句中再加一个条件就可以了

WITH cte2 AS
(
SELECT OrderId , merchantId , uniqueId, status
ROW_NUMBER() OVER (PARTITION BY OrderId , merchantId , iniqueId ORDER BY
CASE status 
WHEN 'ORDERED' THEN 4
WHEN 'SHIPPED' THEN 3
WHEN 'CANCELLED' THEN 2
WHEN 'RETURNED' THEN 1
ELSE 5
END 
) AS rn
FROM table1
) 
Update rdyip 
SET rdyip.is_latest = 'T'
FROM table rdyip
inner JOIN 
cte2 
on 
rdyip.OrderId = cte2.OrderId and 
rdyip.MerchantId = cte2.MerchantId and 
rdyip.uniqueId = cte2.uniqueId 
where cte2.rn=1 and rdyip.status=cte2.status