我有这张桌子:
+------+----------+
| id | position |
+------+----------+
| 1 | 38 |
+------+----------+
| 5 | 102 |
+------+----------+
| 12 | 112 |
+------+----------+
| 13 | 142 |
+------+----------+
并想“重新平衡”排名栏,如下所示
+------+----------+
| id | position |
+------+----------+
| 1 | 0 | (i=0 * 128)
+------+----------+
| 5 | 128 | (i=1 * 128)
+------+----------+
| 12 | 256 | (i=2 * 128)
+------+----------+
| 13 | 384 | (i=3 * 128)
+------+----------+
我也将职位范围划分为这样的列表:
+------+---------------------+
| id | list_id | position |
+------+---------------------+
| 1 | 1 | 10 |
+------+---------------------+
| 5 | 1 | 22 |
+------+----------+----------+
| 12 | 2 | 8 |
+------+----------+----------+
| 13 | 2 | 18 |
+------+----------+----------+
我想我需要知道SQL更新语句中当前项目的索引,或者使用子查询?
您将如何去做?
UPDATE mytable
SET position = s.new_position
FROM (
SELECT
id,
(row_number() over (ORDER BY id) - 1) * 128 as new_position
FROM mytable
) s
WHERE mytable.id = s.id;
使用窗口功能 row_number
使您可以向数据集添加行数(如果您要称呼它为“索引”)。它以1开头,所以我减去1再乘以128。
更新的问题(在第三个表中添加了list_id
):
如果要对每个列表进行单独的位置计数,则必须在窗口功能(PARTITION BY list_id
)中添加一个窗口框架:
UPDATE mytable
SET position = s.new_position
FROM (
SELECT
id,
(row_number() over (PARTITION BY list_id ORDER BY id) - 1) * 128 as new_position
FROM mytable
) s
WHERE mytable.id = s.id;
结果是:
id list_id position
1 1 0
5 1 128
12 2 0
13 2 128
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句