SQL调用临时表中的最大行号 [英] SQL call Max row number from a temp table

问题描述

在临时表中，只有两列可用.我想获取每个负载的最新ID，如下图所示.

In the temp table there are only two columns available. I would like to get the most recent ID for each load, as shown in the picture below.

我已经尝试过了，但是并没有给我我想要的答案.

I have tried this but it doesn't give me the answer I need.

select max(rn_plus_1),a.load, a.id from( select a.load,
   a.id,
   ROW_NUMBER() over(order by a.id desc) rn from max_num a group by load
   , id) a

推荐答案

TEMP_TABLE缺少顺序主键或任何其他插入顺序指示符.因此，不可能使用表本身的列来获取LOAD的最新ID.

TEMP_TABLE lacks a sequential primary key or any other indicator for order of insertion. So it is not possible to get the latest ID for a LOAD using the columns of the table itself.

但是，只有一个选项:

However, there is one option: ORA_ROWSCN(). This is a pseudo-column which identifies the System Change Number for the transaction which changed the table. So we can reconstruct the order of insertion by sorting the table on ORA_ROWSCN.

有一些警告:

1. 默认情况下，SCN适用于块级别.因此，块中的所有行都具有相同的SCN.对于宽桌子来说，这已经足够好了，但是对于像TEMP_TABLE这样的两列玩具来说，则毫无希望.我们只能在行级别跟踪SCN，但前提是该表是使用ROWDEPENDENCIES创建的.默认值为NOROWDEPENDENCIES.不幸的是，我们不能在这里使用ALTER TABLE.您需要删除并重新创建表(*)才能启用ROWDEPENDENCIES.
2. SCN适用于交易.这意味着该解决方案仅在将TEMP_TABLE中的每一行插入单独的事务中时才有效.
3. 显然，只有在TEMP_TABLE是实际表而不是视图或其他构造的情况下，这才可能.

1. By default the SCN applies to the block level. Consequently all the rows in a block have the same SCN. It's a good enough approximation for wide tables but hopeless for a two-column toy like TEMP_TABLE. We can track SCN at the row level but only if the table is created with ROWDEPENDENCIES. The default is NOROWDEPENDENCIES. Unfortunately, we cannot use ALTER TABLE here. You will need to drop and recreate the table (*) to enable ROWDEPENDENCIES.
2. The SCN applies to the transaction. This means the solution will only work if each row in TEMP_TABLE is inserted in a separate transaction.
3. Obviously this is only possible if TEMP_TABLE is an actual table and not a view or some other construct.

鉴于所有这些条件都得到满足，这里是一个查询，它将为您提供所需的结果集:

Given all these criteria are satisfied here is a query which will give you the result set you want:

select load, id
from ( select load
              , id
              , row_number() over (partition by load order by ora_rowscn desc) as rn
       from temp_table
)
where rn = 1

有关于db< fiddle 的演示.另外，除了使用NOROWDEPENDENCIES定义的TEMP_TABLE之外，相同的演示产生错误的结果.

There is a demo on db<>fiddle. Also, the same demo except TEMP_TABLE defined with NOROWDEPENDENCIES, which produces the wrong result.

(*)如果需要将数据保留在TEMP_TABLE中，则步骤如下:

(*) If you need to keep the data in TEMP_TABLE the steps are:

1. rename TEMP_TABLE to whatever;
2. create table TEMP_TABLE as select * from whatever rowdependencies;
3. drop table whatever;

1. rename TEMP_TABLE to whatever;
2. create table TEMP_TABLE as select * from whatever rowdependencies;
3. drop table whatever;

但是，现有行的SCN将相同.如果那很重要，那么您将必须按照希望保留的顺序一次插入每一行，并在每次插入后提交.

However, the SCN will be the same for the existing rows. If that matters you'll have to insert each row one at a time, in the order you wish to preserve, and commit after each insert.

这篇关于SQL调用临时表中的最大行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！