Oracle 11下,查询表tgt_borwreturnschedule 中日期最小的并且非最后一条的记录,菜鸟SQL如下:select a.*
from tgt_borwreturnschedule a,
(select t.tradeid, min(t.paydate) paydate
from tgt_borwreturnschedule t
where t.paydate < (select max(paydate)
from tgt_borwreturnschedule t1
where t1.tradeid = t.tradeid)
group by t.tradeid) v
where a.tradeid = v.tradeid
and a.paydate = v.paydate盼指点
from tgt_borwreturnschedule a,
(select t.tradeid, min(t.paydate) paydate
from tgt_borwreturnschedule t
where t.paydate < (select max(paydate)
from tgt_borwreturnschedule t1
where t1.tradeid = t.tradeid)
group by t.tradeid) v
where a.tradeid = v.tradeid
and a.paydate = v.paydate盼指点
我理解的是同一个tradeid下的非最后一条记录。select tradeid, min(paydate),count(tradeid) from tgt_borwreturnschedule
group by tradeid
having count(tradeid) > 1你不会想说的是整个表的非最后一条记录,也就是只有一条记录的时候吧??
因为如果是表中的非最后一条记录的话,那么如果你的表中有相同的两个最小日期,好像就没办法做了啊?
SELECT m.*
FROM (SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY t.tradeid ORDER BY t.paydate) rn,
COUNT(DISTINCT t.paydate) OVER(PARTITION BY t.tradeid) cnt
FROM t) m
WHERE m.rn = 1
AND m.cnt > 1