爱家娃

招聘数据仓库developer收到一份这种简历 | xwork升级到1.2 反而多了些小问题

2007-04-18

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

关键字: oracle 前N 最大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)，那就太幸福了。

21:07
浏览 (2140)
评论 (3)
分类: DB
进入论坛
相关推荐

老二难缠，可能是因为皮蓬的原因，
取第二大的消费金额有以下写法，但是我没有找到最好的办法。因为不仅仅要取第二，还得取最大的和其他一些信息，并且这张表有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

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)