oracle 比较Oracle中同一个表中的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28815811/
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 between rows in same table in Oracle
提问by samistl
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.
我试图找到在同一个表中的行之间进行比较的最佳方法。我编写了一个自联接查询，并且能够提取出费率不同的查询。现在我需要找出费率是增加还是减少了。如果利率上升，这是一个问题。如果减少了，那就没有问题了。
My data looks like this
我的数据看起来像这样
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
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.
所以在我的表中，我应该能够将 ID 1010 编码为 0（没有问题），将 2010 编码为 1（问题），因为费率从 2 月上升到了 4 月。
回答by Jean-Fran?ois Savard
You can achieve this with a select..case
您可以使用 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
See documentation for CASE expressions.
请参阅CASE 表达式的文档。
回答by Tony Hopkinson
select distinct ID from MyData latest
inner join MyData earlier on latest.id = earlier.id
where earlier.date < latest.date and earlier.rate < latest.rate
would be one way to get them unless you really need to select those with no issues?
除非您真的需要选择没有问题的那些，否则将是获得它们的一种方法？
回答by Boneist
Sounds like a case for LAG():
听起来像一个案例LAG()：
with sample_data as (select 1010 id, to_date('02/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
select 1010 id, to_date('03/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
select 1010 id, to_date('04/02/2014', 'mm/dd/yyyy') dt, 4.9 rate from dual union all
select 2010 id, to_date('02/02/2014', 'mm/dd/yyyy') dt, 4.9 rate from dual union all
select 2010 id, to_date('03/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
select 2010 id, to_date('04/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual)
select id,
dt,
rate,
case when rate > lag(rate, 1, rate) over (partition by id order by dt) then 1 else 0 end issue
from sample_data;
ID DT RATE ISSUE
---------- ---------- ---------- ----------
1010 02/02/2014 7.4 0
1010 03/02/2014 7.4 0
1010 04/02/2014 4.9 0
2010 02/02/2014 4.9 0
2010 03/02/2014 7.4 1
2010 04/02/2014 7.4 0
You may want to throw an outer query around that to only display rows that have issue = 1, or perhaps an aggregate query to retrieve id's that have at least one row that has issue = 1, depending on your actual requirements. Hopefully the above is enough for you to work out how to get what you're after.
您可能希望围绕它抛出一个外部查询，以仅显示具有 的行issue = 1，或者可能需要聚合查询来检索具有至少一行的 id issue = 1，具体取决于您的实际需求。希望以上内容足以让您弄清楚如何获得您想要的东西。
回答by Shiva Kumar
select a.&, case when a.rate > b.rate then 'issue' else 'no issue' end from table a join table b on a.ID=b.ID where a.date > b.date;
选择 a.&, case when a.rate > b.rate then 'issue' else 'no issue' end from table a join table b on a.ID=b.ID where a.date > b.date;
