Oracle查询前几条数据的方法 在Oracle中怎样查询前10条记录?
oracle\u53d6\u524d\u51e0\u6761\u6570\u636e\u8bed\u53e51\u3001\u9996\u5148\u5728oracle\u8f6f\u4ef6\u4e2d\uff0c\u53ef\u4ee5\u4f7f\u7528\u4e0b\u9762\u7684 SELECT \u8bed\u53e5\uff1a\uff08\u5176\u4e2d%\u5c31\u662f\u901a\u914d\u7b26\uff0c\u6807\u8bc6\u8868\u8fbe\u5f0f>=1\u4e2a\u5b57\u7b26\uff09\u3002
2\u3001\u4f7f\u7528\u5176\u4ed6\u7684\u901a\u914d\u7b26\u7684SQL\u8bed\u53e5\u5982\u4e0b\u56fe\u793a\u3002
3\u3001\u4f7f\u7528\u5168\u901a\u914d\u7b26\uff0c\u5c31\u53ef\u4ee5\u5b9e\u73b0\u4e00\u4e2a\u5b57\u7b26\u4e32\u662f\u5426\u5305\u542b\u5305\u542b\u67d0\u4e2a\u5b57\u7b26\u4e32\u7684\u67e5\u627e\u4e86\u3002
4\u3001\u901a\u8fc7\u4f7f\u7528not\u5173\u952e\u5b57\u53ef\u4ee5\u4f7f\u7528\u4e0d\u5305\u542b\u67e5\u627e\u3002
5\u3001\u6700\u540e\u5728SQL\u4e2d\uff0c\u53ef\u4f7f\u7528\u4ee5\u4e0b\u901a\u914d\u7b26\uff0c\u5982\u4e0b\u56fe\u6240\u793a\uff0c\u5c31\u5b8c\u6210\u4e86\u3002
\u6839\u636e\u65f6\u95f4\u6761\u4ef6\u6392\u5e8f\uff0c\u53d6\u524d\u5341\u6761\u548c\u540e\u5341\u6761\u3002
1\u3001\u6709\u65f6\u95f4\u5b57\u6bb5\uff0c \u6839\u636e\u65f6\u95f4\u6761\u4ef6\u6392\u5e8f\uff0c\u53d6\u524d\u5341\u6761\u548c\u540e\u5341\u6761
\uff081\uff09\u524d\u5341\u6761\uff1a
select * from (select * from tab_name a order by date_col )
where rownum<11;
\uff082\uff09\u540e\u5341\u6761\uff1a
select * from (select * from tab_name a order by date_col desc)
where rownum<11;
2\u3001\u6ca1\u6709\u65f6\u95f4\u5b57\u6bb5\uff0c \u76f4\u63a5\u6839\u636e\u7269\u7406\u5b58\u50a8\u987a\u5e8f\uff0c\u53d6\u524d\u5341\u6761\u548c\u540e\u5341\u6761
\uff081\uff09\u524d\u5341\u6761\uff1a
select * from (select * from tab_name a order by rownum)
where rownum<11;
\uff082\uff09\u540e\u5341\u6761\uff1a
select * from (select * from tab_name a order by rownuml desc)
where rownum<11;
由于Oracle不支持select top 语句，所以在Oracle中经常是用order by 跟rownum
的组合来实现select top n的查询。
简单地说，实现方法如下所示：
select 列名1 ...列名n from(select 列名1 ...列名n
from 表名 order by 列名1)where rownum <=N(抽出记录数)
order by rownum asc
如：select id,name from (select id,name from student order by name) where rownum<=10 order by rownum asc
按姓名排序取出前十条数据
附：取100-150条数据的方法1.
最佳选择：利用分析函数
row_number() over ( partition by col1 order by col2 )比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
) where rn between 100 and 150;
2. 使用rownum 虚列
select tname,tabtype from (
select tname,tabtype,rownum rn from tab
绛旓細濡傦細select id,name from (select id,name from student order by name) where rownum<=10 order by rownum asc 鎸夊鍚嶆帓搴忓彇鍑哄墠鍗佹潯鏁版嵁 闄勶細鍙100-150鏉℃暟鎹殑鏂规硶1.鏈浣抽夋嫨锛氬埄鐢ㄥ垎鏋愬嚱鏁 row_number() over ( partition by col1 order by col2 )姣斿鎯冲彇鍑100-150鏉¤褰,鎸夌収tname鎺掑簭 ...
绛旓細鍙互鐢╮ownum鏉ユ煡璇 渚嬪锛歴elect * from 鐢ㄦ埛鍚.琛ㄥ悕 where rownum <= 100鍒鏌ヨ鍓100鏉℃暟鎹锛oracle浼氭牴鎹煡璇㈢粨鏋滅瓫閫夊墠100鏉★紝璁板緱濡傛灉澧炲姞鏌ヨ鏉′欢锛宱racle棣栧厛浼氭牴鎹煡璇㈡潯浠惰繘琛岀瓫閫夛紝鐒跺悗鍐嶅彇鍓100鏉★紝鑰屼笉鏄瓫閫100鏉″悗鎵嶆牴鎹煡璇㈡潯浠惰繘琛岀瓫閫 ...
绛旓細oracle涓煡璇㈠墠8鏉¤褰曢渶瑕佺敤rownum 濡俥mp琛ㄤ腑鏈夊涓嬫暟鎹細鐜拌鏌ヨ姝よ〃涓墠8鏉¤褰曪紝鍙敤濡備笅璇彞锛歴elect * from emp where rownum<=8;鏌ヨ缁撴灉锛
绛旓細鍦∣RACLE涓煡璇㈠墠100鏉¤褰曠殑璇彞鐨勪功鍐欐柟娉曟槸锛proc sql outobs=100锛泂elect * from table1锛泀uit銆傜敤SAS鍐橲QL灏卞簲璇ョ敤瀹冮噷闈㈣嚜甯︾殑涓浜涘姛鑳斤紝鍙兘浼氭洿绠鍗曘傛煡璇竴涓〃涓墍鏈夊瓧娈甸兘鐩稿悓鐨勮褰曪細 姣斿鐜板湪鏈変竴浜哄憳琛 锛堣〃鍚嶏細peosons锛 鑻ユ兂灏嗗鍚嶃佺紪鍙枫佷綇鍧杩欎笁涓瓧娈靛畬鍏ㄧ浉鍚岀殑璁板綍鏌ヨ鍑烘潵锛...
绛旓細oracle涓墠N鏉℃暟鎹鍙敤row_number鏉ュ疄鐜般傚琛ㄤ腑鏁版嵁锛氱幇鍦ㄨ姹傛寜鐓D鍊掑簭锛屽彇鍑哄墠鍗佷綅锛歰racle涓墽琛屾柟娉曪細select t.id,t.name from(select test.*,row_number() over (order by id desc) rn from test) twhere rn<=10;缁撴灉锛
绛旓細閫氳繃rownum灏忎簬绛変簬10鑾峰彇鍓10鏉¤褰 绀轰緥锛歋ELECT * FROM 琛ㄥ悕 WHERE ROWNUM<=10琛ュ厖锛歊OWNUM鏄竴涓搴忓垪锛屾槸oracle鏁版嵁搴浠庢暟鎹枃浠舵垨缂撳啿鍖轰腑璇诲彇鏁版嵁鐨椤哄簭銆傚畠鍙栧緱绗竴鏉¤褰曞垯rownum鍊间负1锛岀浜屾潯涓2锛屼緷娆＄被鎺ㄣ傚皬浜庣瓑浜10锛屽垯灏变細鍙彇鍓10鏉¤褰曘
绛旓細MySql鏌ヨ鍓10鏉℃暟鎹sql璇彞涓猴細select * from table_name limit 0,10 銆傞氬父0鏄彲浠ョ渷鐣ョ殑锛岀洿鎺ュ啓鎴 limit 10銆0浠ｈ〃浠庣0鏉¤褰曞悗闈㈠紑濮嬶紝涔熷氨鏄粠绗竴鏉″紑濮嬨
绛旓細鏂规硶涓锛 select top 10 * from 琛ㄥ悕 order by 鍒楀悕 鏂规硶浜岋細 select top 10 percent * from 琛ㄥ悕 order by 鍒楀悕 鏂规硶涓锛氭槸鎸夌収鎸囧畾鍒楀悕鎺掑簭鍚庯紝鍙栧墠鍗佷釜鏂规硶浜岋細鎸夌収鎸囧畾鍒楀悕鎺掑簭鍚庯紝鍙栨绘暟鐨10% 甯屾湜鑳藉婊¤冻鎮ㄧ殑闇姹傦紝璋㈣阿閲囩撼 ...
绛旓細sqlserver涓彲浠ョ敤top n 鐨勬柟娉,oracle涓敤rownum,浣嗗鏋滃彧鐢╮ownum浼氶殢鏈哄彇鏁版嵁,濡傛灉鎯虫寜涓瀹氶『搴忓彇鍓嶅嚑鏉℃暟鎹鍒欏彲杩欐牱鍐:select * from (select鍒 from 琛 where 鏉′欢 order by鍒 desc) where rownum<鍒楁暟select * from (select rownum r ,* from test) ttwhere tt.r 50 and tt.r <= 100; (鏈濂...
绛旓細鍦ㄤ娇鐢ㄦ暟鎹簱鏌ヨ璇彞鐨勮繃绋嬩腑锛屾垜浠粡甯搁渶瑕佽繑鍥炴煡璇㈢粨鏋滅殑鍓嶅嚑鏉鎴栬呬腑闂鍑犳潯鏁版嵁锛屼笅闈㈡槸鎴戞荤粨鐨勫嚑绉嶆暟鎹簱甯哥敤鐨勬柟娉曪細锛坱able鏄暟鎹簱琛ㄥ悕锛宼able2鏄痶able琛ㄧ殑鍒悕锛1銆丼QLServersqlserver鏀寔top鍏抽敭瀛楋紝杩斿洖鍓嶈嫢骞叉潯鏁版嵁銆俿elect top 5 * from table;// 杩斿洖鍓5琛屾暟鎹2銆丮ySQLmysql鏀寔limit锛...
