五条 select * from ( select a.* ,row_number() over(order by one desc) rk from test a ) where rk<=5
create table tmp as select rowid no from test where rownum<6 order by desc; select * from test,tmp where test.rowid=tmp.no; drop table tmp;
select top 3 one,two from test order by one desc SQL2000里就这样就可以了
select top 5 one,two from test order by one desc
select * from (select one from test order by one desc) where rownum<=5
select * from (select * from test order by one desc, two desc) where rownum<4; 也可以达到目的有那位高手可以说明这句和liuyi8903(西西)的sql那一个的效率更好吗?为什么? 谢谢指教
刚刚测试了一个大表.应该是select * from (select * from test order by one desc, two desc) where rownum<4 快些!
select * from (select one from test order by one desc) where rownum<=5;
select * from ( select a.* ,row_number() over(order by one desc) rk from test a ) where rk<=5
select * from test where rownum <=5 order by one desc
select * from test where rownum <=5 order by one desc
select * from test where rownum <=5 order by one desc这种是不行的,rownum<=5取出的是未排序前的头5条 select * from (select one from test order by one desc) where rownum<=5是正解
select * from (
select a.* ,row_number() over(order by one desc) rk from test a
)
where rk<=5
select * from test,tmp where test.rowid=tmp.no;
drop table tmp;
SQL2000里就这样就可以了
(select one from test order by one desc)
where rownum<=5
也可以达到目的有那位高手可以说明这句和liuyi8903(西西)的sql那一个的效率更好吗?为什么?
谢谢指教
select a.* ,row_number() over(order by one desc) rk from test a
)
where rk<=5
where rownum <=5
order by one desc
where rownum <=5
order by one desc
select * from (select one from test order by one desc) where rownum<=5是正解