表结构:(每个人每次的交易都会存入这张表)
table1:
type: varchar2 number date number varchar2
tranId tranSrlNum tranDate tranAmt pID
1 1 10/10/2011 20 1234567
1 2 10/10/2011 10 1234567
2 1 10/10/2011 10 1234567
2 2 10/10/2011 10 1234567
2 3 10/10/2011 15 1234567 -- 目标记录
1 1 09/09/2011 10 1234567
1 2 09/09/2011 5 1234567
1 1 08/09/2011 6 1234567
.......
1 1 10/10/2011 100 8888888
.......tranId tranSrlNum tranDate 是该表的联合主键, 求每个人最近发生的一次交易记录。如:pID = 1234567(外键,唯一表示一个人), 怎么把目标记录选出来啊 ? 用一条sql 语句实现select *
from table1
where (to_char(tranDate,'dd-mm-yyy')||(trim(tranId))||(trim(tranSrlNum)) = (
select max((to_char(tranDate,'dd-mm-yyy')||(trim(tranId))||(trim(tranSrlNum)) )
from table1
where pID = '1234567'
)上面的得到了正确结果,但是用到自己的function中,加了这段逻辑,结果跑了很长时间没跑出结果,求解!
table1:
type: varchar2 number date number varchar2
tranId tranSrlNum tranDate tranAmt pID
1 1 10/10/2011 20 1234567
1 2 10/10/2011 10 1234567
2 1 10/10/2011 10 1234567
2 2 10/10/2011 10 1234567
2 3 10/10/2011 15 1234567 -- 目标记录
1 1 09/09/2011 10 1234567
1 2 09/09/2011 5 1234567
1 1 08/09/2011 6 1234567
.......
1 1 10/10/2011 100 8888888
.......tranId tranSrlNum tranDate 是该表的联合主键, 求每个人最近发生的一次交易记录。如:pID = 1234567(外键,唯一表示一个人), 怎么把目标记录选出来啊 ? 用一条sql 语句实现select *
from table1
where (to_char(tranDate,'dd-mm-yyy')||(trim(tranId))||(trim(tranSrlNum)) = (
select max((to_char(tranDate,'dd-mm-yyy')||(trim(tranId))||(trim(tranSrlNum)) )
from table1
where pID = '1234567'
)上面的得到了正确结果,但是用到自己的function中,加了这段逻辑,结果跑了很长时间没跑出结果,求解!
select *
from( select row_number()over(partition by pID order by tranDate desc) row_,
t.*
from table1)
where row_ = 1;
你根据你自己的业务情况,改改吧。
from( select row_number()over(partition by pID order by tranDate desc) row_,
t.*
from table1
)
where row_ = 1 and pID = '1234567'
from( select row_number()over(partition by pID order by tranDate desc) row_,
t.*
from table1)
where row_ = 1
and pid='' --加条件
(select t.pid,t.trandate,t.tranid,t.transrlnum,t.tranAmt,
rank() over(partition by t.pid order by t.trandate desc,t.tranid desc,t.transrlnum desc) r
from table1 t) nt
where nt.r=1 and nt.pid='1234567'测试通过,给分吧。