表t_jyjl(ID varchar2(64)key, cardNumber varcher(32),count Number,onDate DateTime)
取出表t_jyjl中所有cardNumber的最近一次交易数量Number
我的写法如下,但是效率比较低,请高手指点下select a.* from t_jyjl a,(
select cardNumber,max(onDate)
from t_jyjl
group by cardNumber
)b
where a.cardNumber=b.cardNumber and a.onDate=b.onDate
取出表t_jyjl中所有cardNumber的最近一次交易数量Number
我的写法如下,但是效率比较低,请高手指点下select a.* from t_jyjl a,(
select cardNumber,max(onDate)
from t_jyjl
group by cardNumber
)b
where a.cardNumber=b.cardNumber and a.onDate=b.onDate
from
(
select *, DENSE_RANK() over(partition by cardnumber order by ondate desc) topN
from t_jyjl
)
where topN = 1你的表,在cardnumber上有索引么?把执行计划帖出来
不过我测试过,
如果你的ondate上有索引
在大数据量下,可能没你写的语句快
SELECT aa.*
FROM (SELECT a.*,
ROW_NUMBER () OVER (PARTITION BY cardnumber ORDER BY ondate DESC)
rn
FROM t_jyjl)
WHERE rn = 1