select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") order by col1,col3;
返回结果有1万之多,想要看top 100,SQL应如何写?
MySQL:select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") order by col1,col3 limit 100;Oracle使用rownum:select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") and rownum<101 order by col1,col3; 肯定是不对的。
目前解决方法为:
select * from (select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") order by col1,col3)t where rownum<101;大家讨论下还有其他方式吗?否则计算TOP N岂不有些傻。
返回结果有1万之多,想要看top 100,SQL应如何写?
MySQL:select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") order by col1,col3 limit 100;Oracle使用rownum:select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") and rownum<101 order by col1,col3; 肯定是不对的。
目前解决方法为:
select * from (select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") order by col1,col3)t where rownum<101;大家讨论下还有其他方式吗?否则计算TOP N岂不有些傻。
select * from (select col1,col2 from tbl1 where col3=val3 and col4 in("a1","a2","a3") order by col1,col3)t where rownum<101;Oracle中 肯定是先order by 再rownum的,不然取出来不是正确值!
是本来就这样,多写几个字而已,TOP N和LIMIT就一定好么???
TOP N:超市里每类商品月销售前100名。非MSSQL的TOP先排序 or 先取值的问题,不便讨论孰优孰劣。 有方法实现就好。
top n和limit只是方便而已。