表结构:
type: varchar2 number date number varchar2
table1:
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 1234567tranId tranSrlNum tranDate 是该表的联合主键,只知道pID = 1234567, 怎么把目标记录选出来啊 ? 用一天条sql 语句实现,下面尝试自己写了一下,重复代码太多,有什么好的分析函数可以用吗?select *
from table1
where tranSrlNum =
(select max(tranSrlNum)
from table1
where pID = 1234567
and tranId =
(select max(tranId)
from table1
where pID = 1234567
and tranDate = (
select max(tranDate)
from table1
where pID = 1234567
)
)
and tranId =
(select max(tranId)
from table1
where pID = 1234567
and tranDate = (
select max(tranDate)
from table1
where pID = 1234567
)
)
and tranDate = (
select max(tranDate)
from table1
where pID = 1234567
)
type: varchar2 number date number varchar2
table1:
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 1234567tranId tranSrlNum tranDate 是该表的联合主键,只知道pID = 1234567, 怎么把目标记录选出来啊 ? 用一天条sql 语句实现,下面尝试自己写了一下,重复代码太多,有什么好的分析函数可以用吗?select *
from table1
where tranSrlNum =
(select max(tranSrlNum)
from table1
where pID = 1234567
and tranId =
(select max(tranId)
from table1
where pID = 1234567
and tranDate = (
select max(tranDate)
from table1
where pID = 1234567
)
)
and tranId =
(select max(tranId)
from table1
where pID = 1234567
and tranDate = (
select max(tranDate)
from table1
where pID = 1234567
)
)
and tranDate = (
select max(tranDate)
from table1
where pID = 1234567
)
where rowid=(select max(rowid) from table1 where pid='1234567')
(不过,需要主键的变化规则是:日期变化后再变化tranId,接着tranSrlNum发生变化)
select * from table1 where tranDate||'-'||tranId||'-'||tranSrlNum=(select max(tranDate||'-'||tranId||'-'||tranSrlNum) from table1 where pid='1234567');
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中,由于cursor 选出的pID 过多,结果跑了很长时间没跑出结果,有什么更高效的方法吗? ~~~~
用二楼的试下。。应该是可以行得通的。