oracle 比较记录集中的 2 个连续行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4040718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
comparing 2 consecutive rows in a recordset
提问by ken
Currently,I have this objective to meet. I need to query the database for certain results. After done so, I will need to compare the records: For example: the query return me with 10 rows of records, I then need to compare: row 1 with 2, row 2 with 3, row 3 with 4 ... row 9 with 10.
目前，我有这个目标要实现。我需要查询数据库以获得某些结果。这样做之后，我需要比较记录：例如：查询返回我有 10 行记录，然后我需要比较：第 1 行与 2，第 2 行与 3，第 3 行与 4 ...第 9 行与 10。
The final result that I wish to have is 10 or less than 10 rows of records.
我希望得到的最终结果是 10 行或少于 10 行记录。
I have one approach currently. I do this within a function, hand have the variables call "previous" and "current". In a loop I will always compare previous and current which I populate through the record set using a cursor.
我目前有一种方法。我在一个函数中执行此操作，手动将变量称为“previous”和“current”。在循环中，我将始终比较我使用游标通过记录集填充的先前和当前。
After I got each row of filtered result, I will then input it into a physical temporary table. After all the results are in this temporary table. I'll do a query on this table and insert the result into a cursor and then returning the cursor.
得到每一行过滤结果后，再将其输入到物理临时表中。毕竟结果都在这个临时表中。我将对该表进行查询并将结果插入游标，然后返回游标。
The problem is: how can I not use a temporary table. I've search through online about using nested tables, but somehow I just could not get it working.
问题是：我怎么能不使用临时表。我在网上搜索过关于使用嵌套表的信息，但不知何故我无法让它工作。
How to replace the temp table with something else? Or is there other approach that I can use to compare the row columns with other rows.
如何用其他东西替换临时表？或者我可以使用其他方法将行列与其他行进行比较。
EDIT
编辑
So sorry, maybe I am not clear with my question. Here is a sample of the result that I am trying to achieve.
很抱歉，也许我的问题不清楚。这是我试图实现的结果示例。
TABLE X
表十
Column A B C D
100 300 99 T1
100 300 98 T2
100 300 97 T3
100 100 97 T4
100 300 97 T5
101 11 11 T6
ColumnAis the primary key of the table. ColumnAhas duplicates because table X is an audit table that keep tracks of all changes.column D acts as the timestamp for that record.
ColumnA是表的主键。ColumnA有重复项，因为表 X 是一个跟踪所有更改的审计表。D 列充当该记录的时间戳。
For my query, I am only interested in changes in column A,B and D. After the query I would like to get the result as below:
对于我的查询，我只对 A、B 和 D 列的更改感兴趣。查询后，我希望得到如下结果：
Column A B D
100 300 T1
100 100 T4
100 300 T5
101 11 T6
回答by guigui42
I think Analytics might do what you want :
我认为分析可能会做你想做的事：
select col1, col2, last(col1) over (order by col1, col2) LASTROWVALUE
from table1
this way, LASTROWVALUE will contain de value of col1 for the last row, which you can directly compare to the col1 of the current row.
这样，LASTROWVALUE 将包含最后一行的 col1 的 de 值，您可以直接将其与当前行的 col1 进行比较。
Look this URL for more info : http://www.orafaq.com/node/55
查看此 URL 了解更多信息：http: //www.orafaq.com/node/55
回答by ankit
SELECT ROW_NUMBER() OVER(ORDER BY <Some column name>) rn,
Column1, <Some column name>, CompareColumn,
LAG(CompareColumn) OVER(ORDER BY <Some column name>) PreviousValue,
LEAD(CompareColumn) OVER(ORDER BY <Some column name>) NextValue,
case
when CompareColumn != LEAD(CompareColumn) OVER(ORDER BY <Some column name>) then CompareColumn||'-->'||LEAD(CompareColumn) OVER(ORDER BY <Some column name>)
when CompareColumn = LAG(CompareColumn) OVER(ORDER BY <Some column name>) then 'NO CHANGE'
else 'false'
end
FROM <table name>
You can use this logic in a loop to change behaviour.
您可以在循环中使用此逻辑来更改行为。
回答by Rene
Hi It's not very clear what exactly yuo want to accomplish. But maybe you can fetch the results of the original query in a PLSQL collection and use that to do your comparison.
嗨 目前还不是很清楚你到底想要完成什么。但是也许您可以在 PLSQL 集合中获取原始查询的结果并使用它来进行比较。
回答by slugster
What exactly are you doing the row comparison for? Are you looking to eliminate duplicates, or are you transforming the data into another form and then returning that?
你到底在做什么行比较？您是希望消除重复项，还是将数据转换为另一种形式然后返回？
To eliminate duplicates, look to use GROUP BY or DISTINCT functionality in your SELECT.
要消除重复项，请在 SELECT 中使用 GROUP BY 或 DISTINCT 功能。
If you are iterating over the initial data and transforming it in some way then it is hard to do it without using a temporary table - but what exactly is your problem with the temp table? If you are concerned about the performance of a cursor then maybe you could do one outer SELECT that compares the results of two inner SELECTs - but the trick is that the second SELECT is offset by one row, so you achieve the requirement of comparing row 1 against row2, etc.
如果您正在迭代初始数据并以某种方式对其进行转换，那么在不使用临时表的情况下很难做到这一点 - 但是您的临时表到底有什么问题？如果您担心游标的性能，那么也许您可以执行一个外部 SELECT 来比较两个内部 SELECT 的结果 - 但诀窍是第二个 SELECT 偏移了一行，因此您实现了比较第 1 行的要求针对 row2 等
回答by Amr H. Abd Elmajeed
I think you are complicating things with the temp table. It can be made using a cursor and 2 temporary variables. Here is the pseudo code:
我认为您正在使临时表复杂化。它可以使用一个游标和 2 个临时变量来实现。这是伪代码：
declare
v_temp_a%xyz;
v_temp_b%xyz;
i number;
cursor my_cursor is select xyz from xyz;
begin
i := 1;
for my_row in my_cursor loop
if (i = 1)
v_temp_a := my_row;
else
v_temp_b := v_temp_a;
v_temp_a := my_row;
/* at this point v_temp_b has the previous row and v_temp_a has the currunt row
compare them and put whatever logic you want */
end if
i := i + 1;
end loop
end
