在Oracle中同一表中的行之间进行比较 [英] Comparing between rows in same table in Oracle

问题描述

我正在尝试找到在同一表中的行之间进行比较的最佳方法。
我编写了一个自我联接查询，并且能够提取费率不同的查询。现在，我需要确定费率是增加还是减少。如果费率提高，那就成问题了。如果它减少了，那么就没有问题。

I am trying to find the best way to compare between rows in the same table. I wrote a self join query and was able to pull out the ones where the rates are different. Now I need to find out if the rates increased or decreased. If the rates increased, it's an issue. If it decreased, then there is no issue.

我的数据看起来像这样

ID       DATE           RATE
1010     02/02/2014      7.4
1010     03/02/2014      7.4
1010     04/02/2014      4.9
2010     02/02/2014      4.9
2010     03/02/2014      7.4
2010     04/02/2014      7.4

因此在我的表中，我应该能够将ID 1010编码为0（无问题），将2010编码为1（问题），因为汇率从2月提高到4月。

So in my table, I should be able to code ID 1010 as 0 (no issue) and 2010 as 1 (issue) because the rate went up from feb to apr.

推荐答案

使用select..case

You can achieve this with a select..case

select case when a.rate > b.rate then 'issue' else 'no issue' end
from yourTable a
join yourTable b using(id)
where a.date > b.date

请参见有关CASE表达式的文档。

这篇关于在Oracle中同一表中的行之间进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！