mtable:
gendate timetag num
20051215 21 32
20051215 21 35
20051215 20 55
20051214 42 25
20051214 41 31
20051214 41 33
需要得到:在最近一天里,timetag最大时的num之和,即32+35。
上次有位朋友的sql语句是这样的:select totalnum from (select gendate,timetag,sum(num) as totalnum from mtable group by gendate,timetag order by gendate desc,timetag desc)where rownum=1但是由于其每次都会sum所有的num,最后再做排序,所以当数据量很大的时候效率不高。
还有一种烦琐的写法是这样:select sum(num) from mtable where gendate=(select max(gendate) from mtable) and timetag=(select max(timetag) from mtable where gendate=(select max(gendate) from mtable))好像效率也不高。用oracle不久,函数都不熟,不知道有没有其他好的方法。谢谢。
gendate timetag num
20051215 21 32
20051215 21 35
20051215 20 55
20051214 42 25
20051214 41 31
20051214 41 33
需要得到:在最近一天里,timetag最大时的num之和,即32+35。
上次有位朋友的sql语句是这样的:select totalnum from (select gendate,timetag,sum(num) as totalnum from mtable group by gendate,timetag order by gendate desc,timetag desc)where rownum=1但是由于其每次都会sum所有的num,最后再做排序,所以当数据量很大的时候效率不高。
还有一种烦琐的写法是这样:select sum(num) from mtable where gendate=(select max(gendate) from mtable) and timetag=(select max(timetag) from mtable where gendate=(select max(gendate) from mtable))好像效率也不高。用oracle不久,函数都不熟,不知道有没有其他好的方法。谢谢。
SELECT SUM(mtable.num)
FROM mtable
JOIN (SELECT (SELECT MAX(gendate) FROM mtable) maxgendate,
(SELECT MAX(timetag)
FROM mtable
WHERE gendate = (SELECT MAX(gendate) FROM mtable)) maxtimetag
FROM DUAL) t1 ON (mtable.gendate = t1.maxgendate
AND mtable.timetag = t1.maxtimetag)
select gendate,timetag,sum(num) as totalnum
from (
select gendate, timetag, num
from mtable
where gendate=(select max(gendate) from mtable)
)
group by gendate,timetag
)
where rownum=1
select sum(num) from
(select gendate,timetag,num,rank() over(order by gendate desc,timetag desc) rk from mtable)
where rn =1
(select gendate,timetag,num,rank() over(order by gendate desc,timetag desc) rk from mtable)
where rk =1
(select (select max(gendate) from mtable),
(select max(timetag) from mtable where gendate=(select max(gendate) from mtable))
from dual)
//关键还是要有mtable(gendate,timetag)的索引