I'm curious about writing a SQL query in Oracle based on the previous row in a list of returned rows. Basically I have to query our database for all rows that have a ID # of 2460, but the previous row (sorted by date) has an ID # of 2463. Is there any way to do this?
我很好奇在Oracle中基于返回行的列表中的前一行编写SQL查询。基本上，我必须查询数据库中所有ID为2460的行，但是前面的行(按日期排序)的ID为2463。有什么办法吗?
Sorry if this is confusing or a stupid question I'm very new and not really good at these sort of things. I'll be happy to clarify anything that needs clarifying.
不好意思，如果这是一个令人困惑的问题或者一个愚蠢的问题，我很新，不擅长这类事情。我很乐意澄清任何需要澄清的事情。
Thanks!
谢谢!
Edit:
编辑:
Here's the query I'm running based on Gordon's answer along with a screenshot of the results I'm getting vs the results I'm wanting.
这是我根据戈登的回答运行的查询，以及我得到的结果与我想要的结果的屏幕截图。
select *
from (select activitytranledger.*, lag(reasontype) over (order by trandate) as prev_reason from activitytranledger) activitytranledger
where trandate between to_date('02/7/2017','MM/DD/YYYY')
and to_date('02/8/2017','MM/DD/YYYY')
and reasontype = 2460
and prev_reason = 2463
order by trandate
;
I'll then take the location # and query it for the specific day.
然后，我将获取位置#并查询它的具体日期。
select *
from activitytranledger
where location = 5777
and trandate between to_date('02/7/2017','MM/DD/YYYY')
and to_date('02/8/2017','MM/DD/YYYY')
order by trandate
;
Using the "Transid" I can find the specific row that was output by the first query. However, it does not seem to be giving me the desired results.
使用“Transid”，我可以找到第一个查询输出的特定行。然而，它似乎并没有给我理想的结果。
Wanted results (Notice how the row directly above the row with a "Reasontype" of 2460 has a reason type of 2463)
需要的结果(请注意，位于2460的“推理类型”之上的行如何具有2463的推理类型)
Current results (The row I highlighted should have the 2463 in the column I've pointed to)
当前的结果(我强调的行应该在我所指向的列中有2463)
Edit 2: switched 2463 and 2460
编辑2:切换到2463和2460
2 个解决方案
#1
2
This is one interpretation of your question. Use lag() and a subquery:
这是对你问题的一种解释。使用lag()和子查询:
select t.*
from (select t.*, lag(id) over (order by date) as prev_id
from t
) t
where id = 2463 and prev_id = 2460;
#2
0
So, it turns out I'm an idiot. @GordonLinoff's solution worked perfectly I just simply forgot to add the location # to the query below is the final query that I came up with. I'm able to replace the location # with my variable and can now insert it into my loop and it works like a charm. Thanks all that contributed!
原来我是个白痴。@GordonLinoff的解决方案工作得很好，我只是忘记将location #添加到下面的查询中，这是我提出的最后一个查询。我可以用我的变量替换位置#，现在可以将它插入到循环中，它就像一个魔咒一样。谢谢所有的贡献!
select *
from (select activitytranledger.*, lag(reasontype) over (order by trandate) as
prev_reason from activitytranledger) activitytranledger
where trandate between to_date('02/7/2017','MM/DD/YYYY')
and to_date('02/8/2017','MM/DD/YYYY')
and location = 5777
and reasontype = 2460
and prev_reason = 2463
order by trandate
;
#1
2
This is one interpretation of your question. Use lag() and a subquery:
这是对你问题的一种解释。使用lag()和子查询:
select t.*
from (select t.*, lag(id) over (order by date) as prev_id
from t
) t
where id = 2463 and prev_id = 2460;
#2
0
So, it turns out I'm an idiot. @GordonLinoff's solution worked perfectly I just simply forgot to add the location # to the query below is the final query that I came up with. I'm able to replace the location # with my variable and can now insert it into my loop and it works like a charm. Thanks all that contributed!
原来我是个白痴。@GordonLinoff的解决方案工作得很好，我只是忘记将location #添加到下面的查询中，这是我提出的最后一个查询。我可以用我的变量替换位置#，现在可以将它插入到循环中，它就像一个魔咒一样。谢谢所有的贡献!
select *
from (select activitytranledger.*, lag(reasontype) over (order by trandate) as
prev_reason from activitytranledger) activitytranledger
where trandate between to_date('02/7/2017','MM/DD/YYYY')
and to_date('02/8/2017','MM/DD/YYYY')
and location = 5777
and reasontype = 2460
and prev_reason = 2463
order by trandate
;