[oracle]返回的最大值和值事先

标签: sql Oracle
发布时间: 2017/4/15 18:54:47
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有我跑来告诉我最新的注意积极参与者的查询︰

select notes.applicant_id,
   reg.program_code,
   reg.last_name,
   reg.first_name,
   reg.status_cd,
   MAX(notes.service_date) as "Last Note"
from reg inner join notes on reg.applicant_id=notes.applicant_id
where reg.status_cd='AC'
group by notes.applicant_id, reg.program_code, 
         reg.last_name, reg.first_name, reg.reg_date, 
         reg.region_code, reg.status_cd
order by MAX(notes.service_date)

但我还想此查询以给我的结果 note.service_date 只是之前最高 service_date 以及。

结果将看起来像这

notes.applicant_id   reg.last_name  reg.first_name reg.status_cd  Last Note    Prior Note
 12345                 Johnson          Lori           AC        01-NOV-2011   01-OCT-2011

我在 oracle 工作。

解决方法 1:

您可以使用 lag 函数,或加入与同一个表。

这里是一个简单的例子 (你没 givven 我们数据示例):

create table t as
(select level as id, mod(level , 3) grp, sysdate - level dt
from dual 
connect by level < 100
)

和这里的查询︰

select t2.grp,t1.grp, max(t1.dt) mdt, max(t2.dt) pdt
  from t t1
  join t t2 on t1.dt < t2.dt and t1.grp = t2.grp
 group by t2.grp, t1.grp;

select grp, max(pdt), max(dt)
 from(
 select grp, lag(dt) over (partition by grp order by dt) pdt, dt 
 from t)
 group by grp

这里是一把小提琴


你的情况可能是这样的事情︰

select t.applicant_id, t.program_code, 
         t.last_name, t.first_name, t.reg_date, 
         t.region_code, t.status_cd,
         max(t.dt) as "Last Note",
         max(t.pdt) as "Prev Note"
from (
select notes.applicant_id,
   reg.program_code,
   reg.last_name,
   reg.first_name,
   reg.status_cd,
   notes.service_date as dt,
   lag(notes.service_date)  over (partition by notes.applicant_id,
   reg.program_code,
   reg.last_name,
   reg.first_name,
   reg.status_cd order by notes.service_date) as pdt
from reg inner join notes on reg.applicant_id=notes.applicant_id
where reg.status_cd='AC'
) t
group by t.applicant_id, t.program_code, 
         t.last_name, t.first_name, t.reg_date, 
         t.region_code, t.status_cd
order by MAX(t.dt)
赞助商