select * from tpi_Pay_Amount p1 where p1.paydate=(select Max(p.paydate) from tpi_Pay_Amount p where p.worklist_Id='402880791ad407a2011ad40a9fea0001'and p.paydate<(select Max(p3.paydate) from tpi_Pay_Amount p3)) and p1.worklist_Id='402880791ad407a2011ad40a9fea0001'我想在表tpi_Pay_Amount中得到worklist_Id='402880791ad407a2011ad40a9fea0001'的记录集,并且在此基础上找到paydate第二大的那条记录.其中worklist_Id不是表tpi_Pay_Amount 的主键.谁能帮我写一条效率高的sql语句.谢谢.
十份感谢!!
十份感谢!!
SELECT *
FROM tpi_pay_amount p1
WHERE p1.paydate =
(SELECT MAX (p.paydate)
FROM tpi_pay_amount p
WHERE p.worklist_id = '402880791ad407a2011ad40a9fea0001'
AND p.paydate < (SELECT MAX (p3.paydate)
FROM tpi_pay_amount p3))
AND p1.worklist_id = '402880791ad407a2011ad40a9fea0001'俺只是路过,顺手排版一下的
SELECT * FROM (
SELECT * FROM (
SELECT * FROM tpi_pay_amount
WHERE worklist_id = '402880791ad407a2011ad40a9fea0001'
ORDER BY paydate)
WHERE ROWNUM < 3 ORDER BY ROWNUM DESC)
WHERE ROWNUM = 1
lz把执行计划贴出来,对性能的比较更加直观一些。
SELECT * FROM (
SELECT rownum rn, t.* FROM tpi_pay_amount
WHERE worklist_id = '402880791ad407a2011ad40a9fea0001'
ORDER BY paydate) t
WHERE rn = 2
继续期待.!....
from
(select rownum rn2,t2.*
from
(select rownum rn1,t1.* from tpi_Pay_Amount t1 where t1.worklist_Id = '23453' order by t1.paydate desc) t2) t3
where t3.rn2 = 2;试试这个
(SELECT MAX (paydate) paydate2 FROM tpi_pay_amount p WHERE p.worklist_id = '402880791ad407a2011ad40a9fea0001') p2,
(SELECT MAX (paydate) paydate3 FROM tpi_pay_amount p3) p3
where p1.paydate = p2.paydate2
and p2.paydate < p3.paydate3
and p1.worklist_id = '402880791ad407a2011ad40a9fea0001'
SELECT rownum rn, t.* FROM tpi_pay_amount
WHERE worklist_id = '402880791ad407a2011ad40a9fea0001'
ORDER BY paydate DESC) t
WHERE rn = 2
不是这个吗
lz,你先执行中间的子查询,看出来的结果是什么,是否按照paydate从大到小的顺序排的。如果是这样的,那第二条不就是lz想要的了嘛。
如果子查询出来的结果不是预期,那就是lz给的id错了。
rownum含义解释:
1、rownum是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,
依此类推,这个伪字段可以用于限制查询返回的总行数。
2、rownum不能以任何基表的名称作为前缀。
使用方法:
SQL> select rownum,month,sell from sale where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
rownum MONTH SELL
--------- ------ ---------
1 200001 1000
SQL> select rownum,month,sell from sale where rownum=2;(1以上都查不到记录)
没有查到记录
SQL> select rownum,month,sell from sale where rownum>5;
(由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件不成立,查不到记录)
oracle的rownum是不能等于2的!!!!!!