时间戳

首页 > 解决方案 > Oracle 12 - 在分区上使用 row_number() 重置序列列

sql - Oracle 12 - 在分区上使用 row_number() 重置序列列

问题描述

我有这张表（按产品排序，seq）：

product     group       seq
10             5         1
10             11        5
11             2         3
11             4         4
11             24        5

正如您所看到的，由于一些删除语句，我需要重置 seq 列，并为每个产品/组设置一个从 1 开始的 seq，如下所示：

productnr     group     seq
10             5         1
10             11        2
11             2         1
11             4         2
11             24        3

我尝试使用以下代码创建一个新计数器：

select t.*, row_number() over (partition by t.product order by  t.product ) newSeq
from table t
order by t.product, t.seq;

结果是这样的：

product     group     seq      newSeq
10             5         1         1
10             11        5         2
11             2         3         1
11             4         4         2
11             24        5         3

但是当我尝试使用更新时，我得到了 ora-00907：

update table a set a.seq= 
(
    select row_number() over (partition by z.product order by  z.product ) newSeq
    from table z
    where z.product = a.product and z.seq= a.seq
    order by z.product, z.seq
);

我认为错误是因为order by z.product, z.seqSubselect 中的“”。我无法删除它，因为 newSeq 必须基于旧 Seq，没有它 newSub 可能是错误的。

更新1：

Hier 是没有 orderby 的示例：

select t.*, row_number() over (partition by t.product order by  t.product ) newSeq
from table t
order by t.product, t.seq;

结果：

product       group     seq        newSeq
10             11        5           1 
10             5         1           2
11             4         4           1
11             2         3           2
11             24        5           3

如您所见，newSeq 不是基于没有 order by 的旧 Seq。这意味着当产品 5/组 11 具有比相同产品/组 5 更大的 seq 时，新的 Seq 也应该是这样的。

标签： sqloracle

解决方案

在伪列上使用MERGE语句和关联来有效地自联接：ROWID

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         ROW_NUMBER() OVER (
           PARTITION BY product
           ORDER BY ROWNUM  -- or ORDER BY seq
         ) AS newSeq
  FROM   table_name
) src
ON ( dst.ROWID = src.rid )
WHEN MATCHED THEN
  UPDATE SET seq = src.newSeq;

（注意：您不需要将ORDER BY表达式附加到SELECT语句，因为它不会产生实际效果，因为源查询的顺序在执行过程中无关紧要，MERGE并且如果应用了排序并且 SQL 引擎执行了不必要的工作，则可能会SELECT降低语句的效率.)

其中，对于样本数据：

CREATE TABLE table_name ( product, "GROUP", seq ) AS
SELECT 10,  5, 1 FROM DUAL UNION ALL
SELECT 10,  1, 5 FROM DUAL UNION ALL
SELECT 11,  2, 3 FROM DUAL UNION ALL
SELECT 11,  4, 4 FROM DUAL UNION ALL
SELECT 11, 24, 5 FROM DUAL

然后导致：

SELECT * FROM table_name
产品 团体 序列
10 5 1
10 1 2
11 2 1
11 4 2
11 24 3

db<>在这里摆弄

推荐阅读