Oracle -表中基于列的排序

Question

我有一个包含列的表customer_order

ORD_ID, AI_LN, ITEM

数据如下所示

A100 null 001232
A100 null 108632
A100 null 501632
A200 null 301632
A200 null 601732

我需要根据订单对AI_LN进行编号，这样它就变成

A100 0 001232
A100 1 108632
A100 2 501632
A200 0 301632
A200 1 601732

如何做到这一点？它会是通过PLSQL块实现的吗？

Answer 1

如果您只想选择给定的ai_ln，您可以使用select中的窗口函数：

select ord_id,
    row_number() over (
        partition by ord_id order by item
        ) - 1 as ai_ln,
    item
from your_table;

如果要用它更新表，可以在merge语句中使用它。

merge into your_table t
using (
    select ord_id,
        row_number() over (
            partition by ord_id order by item
            ) - 1 as ai_ln,
        item
    from your_table
    ) s
    on (s.rowid = t.rowid)
when matched
    then
        update
        set t.ai_ln = s.ai_ln;

Answer 2

不需要使用PL/ SQL，这可以在SQL中使用ROW_NUMBER分析函数来完成：

SELECT ORD_ID,
       ROW_NUMBER() OVER ( PARTITION BY ORD_ID
                           ORDER BY ITEM ) -- or ORDER BY ROWNUM
         AS ai_ln,
       ITEM
FROM   customer_order;

如果要更新表，请执行以下操作：

UPDATE customer_table c
SET ai_ln = (
  SELECT rn
  FROM   (
    SELECT ROW_NUMBER() OVER ( PARTITION BY ord_id
                               ORDER BY item )
             AS rn
    FROM   customer_table
  ) r
  WHERE c.ROWID = r.ROWID
);

或MERGE

MERGE INTO customer_table dst
USING ( SELECT ROW_NUMBER() OVER ( PARTITION BY ord_id
                                   ORDER BY item ) AS rn
        FROM   customer_table ) src
ON ( dst.ROWID = src.ROWID )
WHEN MATCHED THEN
  UPDATE SET ai_ln = src.rn;

Answer 3

使用row_number()解析函数：

select ORD_ID
     , row_number() over(partition by ORD_ID order by ITEM ) -1 as AI_LN
     , ITEM
  from table;