如何有效的取前N大记录？

老二难缠，可能是因为皮蓬的原因，
取第二大的消费金额有以下写法，但是我没有找到最好的办法。因为不仅仅要取第二，还得取最大的和其他一些信息，并且这张表有2000万记录，如果分组后再和自己关联，效率肯定非常难看。

Select * From
(Select exp_amt,Id,Rownum idid From
(Select t.*,Rownum Id From etl_dzdinfo t Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' 
Order By exp_amt Desc ) tt) Where idid=2

Select exp_amt,Id From (
Select t.*,Row_number() over(Order By exp_amt Desc) Id From etl_dzdinfo t Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' ) Where Id=2

Select exp_amt,Id From (
Select t.*,rank() over(Partition By main_crd_no  Order By exp_amt Desc) Id From etl_dzdinfo t 
Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' ) Where Id=2

Select exp_amt,Id From (
Select t.*,dense_rank() over(Partition By main_crd_no  Order By exp_amt Desc) Id From etl_dzdinfo t 
Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' ) Where Id=2

Select Max(exp_amt) From (
Select Max(exp_amt) max_amt From etl_dzdinfo t
Where t.main_crd_no='4518100351400218'  And exp_amt Is Not Null And trans_txt = '消费' 
) a,( Select t.* From etl_dzdinfo t
Where t.main_crd_no='4518100351400218'  And exp_amt Is Not Null And trans_txt = '消费') b
Where b.exp_amt < a.max_amt

为什么就没有象max 这样一个简单易用高效的方法呢？ 用起来 就如 maxn(colum,n)，那就太幸福了。

评论

3 楼 eyejava 2007-04-19  

to taya: 和我最后一种写法一样

楼上的写法看不懂，能说下思路吗？

2 楼 JAVA_ED 2007-04-19  

eyejava 写道

老二难缠，可能是因为皮蓬的原因，
取第二大的消费金额有以下写法，但是我没有找到最好的办法。因为不仅仅要取第二，还得取最大的和其他一些信息，并且这张表有2000万记录，如果分组后再和自己关联，效率肯定非常难看。

Select * From
(Select exp_amt,Id,Rownum idid From
(Select t.*,Rownum Id From etl_dzdinfo t Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' 
Order By exp_amt Desc ) tt) Where idid=2

Select exp_amt,Id From (
Select t.*,Row_number() over(Order By exp_amt Desc) Id From etl_dzdinfo t Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' ) Where Id=2

Select exp_amt,Id From (
Select t.*,rank() over(Partition By main_crd_no  Order By exp_amt Desc) Id From etl_dzdinfo t 
Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' ) Where Id=2

Select exp_amt,Id From (
Select t.*,dense_rank() over(Partition By main_crd_no  Order By exp_amt Desc) Id From etl_dzdinfo t 
Where t.main_crd_no='4518100351400218'
And exp_amt Is Not Null And trans_txt = '消费' ) Where Id=2

Select Max(exp_amt) From (
Select Max(exp_amt) max_amt From etl_dzdinfo t
Where t.main_crd_no='4518100351400218'  And exp_amt Is Not Null And trans_txt = '消费' 
) a,( Select t.* From etl_dzdinfo t
Where t.main_crd_no='4518100351400218'  And exp_amt Is Not Null And trans_txt = '消费') b
Where b.exp_amt < a.max_amt

为什么就没有象max 这样一个简单易用高效的方法呢？ 用起来 就如 maxn(colum,n)，那就太幸福了。

function(procedure) recursion   如果DB不支持top rowcount(比如sybase)

declare testCursor cursor for
  select max(x) from XX where x not in (select * from #tmp)
open testCursor
fetch testCursor into @x
  if(@num <= n) begin
     insert into #tmp values...
  end
....

select * from #tmp

1 楼 taya 2007-04-19  

来个bt的试试？

select id
  from etl_dzdinfo t1
 where 1 = 1
   and t1.main_crd_no='4518100351400218'
   and t1.exp_amt = (
                      select max(t2.exp_amt)
                        from etl_dzdinfo t2
                       where 1 = 1
                         and t2.main_crd_no='4518100351400218'
                         and t2.exp_amt <> (
                                            select max(t3.exp_amt)
                                              from etl_dzdinfo t3
                                               and t3.main_crd_no='4518100351400218'
                                           )
                     )

自己随便弄了个表试了试
取第2大还是挺快的
可惜没法做到maxn(colum,n)