表tab,列class, name, seqnum, highpoint,lowpoint
数据
class seqnum highpoint lowpoint name
A 1 100 95 a
A 2 100 90 b
A 3 90 85 c
A 4 70 55 d
B 1 98 88 e
B 2 85 68 f
目的是取出各个class中,seqnum连续但是最高分比前面的seqnum的最低分要低的数据
例子里面的结果是
A 4 70 55 d
B 2 85 68 f当然简单的方法是
select x.* from tab x, tab y where x.class = y.class and x.seqnum = y.seqnum+1 and x.highpoint<y.lowpoint
但是由于tab这个表的数据量非常大,不知能否用分析函数来实现?
数据
class seqnum highpoint lowpoint name
A 1 100 95 a
A 2 100 90 b
A 3 90 85 c
A 4 70 55 d
B 1 98 88 e
B 2 85 68 f
目的是取出各个class中,seqnum连续但是最高分比前面的seqnum的最低分要低的数据
例子里面的结果是
A 4 70 55 d
B 2 85 68 f当然简单的方法是
select x.* from tab x, tab y where x.class = y.class and x.seqnum = y.seqnum+1 and x.highpoint<y.lowpoint
但是由于tab这个表的数据量非常大,不知能否用分析函数来实现?
但按你的要求来看,可能会有其它情况
select class,seqnum,highpoint,lowpoint,name
from(select class,seqnum,highpoint,lowpoint,name,lag(lowpoint,1,111)over (order by class,seqnum) low1
from table order by class,seqnum)
where highpoint<low1 order by class,name;
另外,如果采用双重循环的话,我也能实现,只是双重循环的话,由于数据量大
也不好处理
不知有没有像having子句之类的方法来实现,一个select语句直接筛选出结果
用分析函数的好处是避免的自连接,另外我那个的结果能贴一下吗,我看看哪里的问题
A 1 100 95 a
A 4 70 55 d
B 2 85 68 f
是的,我也在想一层循环很难,不知以后oracle会不会有这种分析的同时进行删除的功能
select class,seqnum,highpoint,lowpoint,name
from(select class,seqnum,highpoint,lowpoint,name,lag(lowpoint,1,0)over (order by class,seqnum) low1
from table order by class,seqnum)
where highpoint<low1 order by class,name;这个执行起来很慢吗,把里层的order by 去了试试还慢吗select class,seqnum,highpoint,lowpoint,name
from(select class,seqnum,highpoint,lowpoint,name,lag(lowpoint,1,0)over (order by class,seqnum) low1
from table)
where highpoint<low1 order by class,name;
改成这样就可以了,效率应该一般吧
select class,seqnum,highpoint,lowpoint,name
from(select class,seqnum,highpoint,lowpoint,name,lag(lowpoint,1,0)over (partition by
class order by seqnum) low1
from tmp_tab)
where highpoint<low1 order by class,name;