我的餐桌如下所示
location rank
location_A 1
location_B 2
location_C 3
location_D 4
location_E 5
location_F 6
・
・
・
并且，最近位置=location_E，第二个最近位置=location_D
所以我想要下面的中餐桌
location rank
location_E 1
location_D 2
我的预期结果如下:location_E和location_D将其排名移动为1和2，其余位置保持其order但滑动其rank
location rank
location_E 1
location_D 2
location_A 3
location_B 4
location_C 5
location_F 6
・
・
有什么好方法可以实现这一点吗？ 谢谢
try 使用带有条件ORDER BY子句的RANK函数执行以下操作:
SELECT location,
RANK() OVER (ORDER BY CASE
WHEN location = 'location_E' THEN 1
WHEN location = 'location_D' THEN 2
ELSE 3
END, rank_) rank_
FROM table_name
如果您想用新的排名更新您的表，请try 以下操作:
WITH ranks AS
(
SELECT location,
RANK() OVER (ORDER BY CASE
WHEN location = 'location_E' THEN 1
WHEN location = 'location_D' THEN 2
ELSE 3
END, rank_) rank_
FROM table_name
)
UPDATE table_name T
SET rank_ = R.rank_
FROM ranks R
WHERE T.location = R.location;
请看demo.