SQL、MySQL、Oracle随机查询语句？ oracle数据库，如何根据概率随机查询取四条数据呢？
SQL\u8bed\u53e5\u5982\u4f55\u5b9e\u73b0\u4ece\u6570\u636e\u5e93\u8868\u4e2d\u67e5\u8be2\u968f\u673a\u6570\u636e\u7684\u8bb0\u5f55\u6570\u636e\u5e93\u7684\u968f\u673a\u67e5\u8be2SQL
1. Oracle,\u968f\u673a\u67e5\u8be220\u6761
select * from
(
select * from \u8868\u540d
order by dbms_random.value
)
where rownum <= 20;
2.MS SQL Server\uff0c\u968f\u673a\u67e5\u8be220\u6761
select top 20 * from \u8868\u540dorder by newid()
3.My SQL:\uff0c\u968f\u673a\u67e5\u8be220\u6761
select * from \u8868\u540d order by rand() limit 20
select * from tableName sample(10) where rownum<5;
sample(10)\u4ee3\u8868\u4ecetableName \u8868\u4e2d\u968f\u673a\u53d6\u523010%\u7684\u6570\u636e rownum<5 \u53d64\u6761
1、Oracle查询结果集，随机排序
select * from tableName order by dbms_random.value();
2、MySQL随机查询，随机排序
SELECT * FROM tableName ORDER BY rand()
3、SQL随机查询，随机排序
SELECT * FROM tableName ORDER BY NEWID()
4、Oracle随机查询20条
select * from
(
select * from tableName order by dbms_random.value
)
where rownum <= 20;
5、My SQL随机查询20条
select * from tableName order by rand() limit 20
6、MS SQL Server随机查询20条
select top 20 * from tableName order by newid()
关于SQL Server SQL语句查询分页数据的解决方案:要求选取 tbllendlist 中 第3000页的记录，每一页100条记录。-----------------------------------方法1：----------select top 100 * from tbllendlistwhere fldserialNo not in(select top 300100 fldserialNo from tbllendlistorder by fldserialNo)order by fldserialNo----------方法2：----------SELECT TOP 100 *FROM tbllendlistWHERE (fldserialNo >(SELECT MAX(fldserialNo)FROM (SELECT TOP 300100 fldserialNoFROM tbllendlistORDER BY fldserialNo) AS T))ORDER BY fldserialNo方法1执行速度比较快！不过，这种做法还是很麻烦，强烈期待微软发明新的可分页的SQL语句来！！！！SQL Server ---------------------------------------------------------------------------------从数据库表中的第M条记录开始取N条记录，利用Top关键字：注意如果Select语句中既有top，又有order by，则是从排序好的结果集中选择：SELECT *FROM ( SELECT Top N *FROM (SELECT Top (M + N - 1) * FROM 表名称 Order by 主键 desc) t1 ) t2Order by 主键 asc例如从表Sys_option（主键为sys_id)中从10条记录还是检索20条记录，语句如下：SELECT *FROM ( SELECT TOP 20 *FROM (SELECT TOP 29 * FROM Sys_option order by sys_id desc) t1) t2Order by sys_id ascOralce数据库--------------------------------------------------------------------------------从数据库表中第M条记录开始检索N条记录SELECT *FROM (SELECT ROWNUM r,t1.* From 表名称 t1 where rownum < M + N) t2where t2.r >= M例如从表Sys_option（主键为sys_id)中从10条记录还是检索20条记录，语句如下：SELECT *FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2Where t2.R >= 10如果你对Oracle数据库分页不是很熟悉的话，本页后面有专门对Oracle分页技术的讲解。My sql数据库---------------------------------------------------------------------------------------------My sql数据库最简单，是利用mysql的LIMIT函数,LIMIT [offset,] rows从数据库表中M条记录开始检索N条记录的语句为：SELECT * FROM 表名称 LIMIT M,N例如从表Sys_option（主键为sys_id)中从10条记录还是检索20条记录，语句如下：select * from sys_option limit 10,20TAG: racle RACLE Server server SERVER sql SQL
SQL随机查询语句，SQL语句生成随机数，如何随机显示sql查询语句查询的数据记录集中的20条，随机取出若干条记录的SQL语句，使用SQL语句取随机数的方法方法如下：SQL Server：
Select TOP N * From TABLE order By NewID()Access：
sql = "select top N * from table order by rnd(" & r & "-id)"
rnd(" & r & "-id) 其中的id是自动编号字段，可以利用其他任何数值来完成，比如用姓名字段(UserName)
select top 10 * from table order by rnd(" & r & "-Len(UserName))注：Id:为你当前表的唯一ID字段名MySql：
Select * From TABLE order By Rand() Limit NorACLE：
select *from (select *from table order by dbms_random.value) where rownum<10
DB2: SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
比如随机插4条
oracle
select * from (select * from 表名 order by dbms_random.value) where rownum<=4
sqlserver
select top 4 * from 表名 order by newid()
mysql
select * from 表名 order by rand() limit 4;
绛旓細浼樼偣锛氭槗鐢ㄦс侀傚悎鍒嗗竷寮忕粍缁囩殑鍙几缂╂с佺敤浜庡喅绛栨敮鎸佺殑鏁版嵁浠撳簱鍔熻兘銆佷笌璁稿鍏朵粬鏈嶅姟鍣ㄨ蒋浠剁揣瀵嗗叧鑱旂殑闆嗘垚鎬с佽壇濂界殑鎬т环姣旂瓑锛涚己鐐癸細鍙兘杩愯鍦ㄥ井杞殑windows骞冲彴锛屾病鏈変笣姣殑寮鏀炬у彲瑷锛沵ysql 浼樼偣锛氳交閲忕骇锛岄儴缃叉柟渚匡紝寮婧愶紝鍏嶈垂锛堟妧鏈湇鍔℃槸瑕佹敹璐圭殑锛夛紝闄ゆ涔嬪锛宮ysql鍑犱箮鎷ユ湁浜oracle鎻愪緵鐨勫ぇ閮ㄥ垎...
绛旓細鏁版嵁搴撴妧鏈紝鏄俊鎭鐞嗙郴缁熴佽嚜鍔ㄥ寲鍔炲叕绯荤粺銆侀攢鍞粺璁＄郴缁熺瓑鍚勭淇℃伅绯荤粺鐨勬牳蹇冮儴鍒嗭紝鏄繘琛岀瀛︾爺绌跺拰鍐崇瓥绠＄悊鐨勯噸瑕佹妧鏈墜娈碉紝甯哥敤鐨勬暟鎹簱鍖呮嫭锛Oracle銆丏B2銆MySQL銆丼QL Server銆丼ybase銆乂F绛夈傜敱浜嶮ySQL鏁版嵁搴撳叿鏈夋ц兘浼樿壇銆佺ǔ瀹氭уソ銆侀厤缃畝鍗曘佹敮鎸佸悇绉嶆搷浣滅郴缁熺瓑浼樺娍锛屾湰绔犱富瑕佽杩癢indows绯荤粺涓婱ySQL...
绛旓細Oracle銆Mysql銆SQL Server銆丄ccess浣滀负鍥涚涓嶅悓鐨勬暟鎹簱绠＄悊绯荤粺锛屽涔犳椂闂寸殑闀跨煭涓庡畠浠湰韬殑澶嶆潅绋嬪害銆佷釜浜虹殑瀛︿範鏂规硶鍜屼粯鍑虹殑鍔姏鏈夊叧銆oracle浣滀负澶у瀷缃戠粶鏁版嵁搴擄紝鐩稿浜庡叾瀹冧笁绉嶆暟鎹簱锛屾槸鍞环鏈鏄傝吹鐨勶紝閫氬父鐢ㄤ簬澶у瀷鍥戒紒鎴栬法鍥藉叕鍙哥瓑銆傚洜鍏朵綋绯荤粨鏋勫鏉傦紝缁勪欢鍜屽懡浠や紬澶氳岄椈鍚嶏紝涔熷洜鍏舵ц兘绋冲畾锛屽畨鍏ㄦ...
绛旓細杩樻湁SQL Service,涓汉璁や负姣旇緝閫傚悎ASP,C++绛夎瑷鐨勯」鐩 寮鍙戝伐鍏锋垜鍙兘璇磋Oracle浜,鎴戠敤鐨勬槸PL SQL銆 闂涓:鎴戞兂鍋氫竴涓暟鎹簱,闇瑕佷粈涔堣蒋浠跺晩?? 浣犺嚜宸辨槸绋嬪簭鍛? 鏁版嵁搴(access銆mysql銆oracle)+宸濇佺綉绔欒瑷(jsp銆乸hp)+鏈嶅姟鍣(tomcat銆乮is) 鍗冲彲銆 浣犳槸涓氬姟浜哄憳? excel銆 浣犳槸绠＄悊浜哄憳? 寤鸿...
绛旓細甯歌鏁版嵁搴,鏄竴濂楀畬鏁寸殑,鎷ユ湁鑷繁鐨勫瓨鍙栨満鍒躲丼QL璇彞棰勫鐞嗘満鍒剁瓑鐨勪竴濂楃▼搴忋傚鍞琈S SQL銆MYSQL銆ORACLE绛夌瓑銆 鑰岄潪甯歌鏁版嵁搴,鏈韩骞朵笉鍏峰瀛樺彇鏈哄埗銆丼QL棰勫鐞嗘満鍒剁瓑,鑰屾槸閫氳繃鍏跺畠鐨勫悇绉嶅悇鏍风殑绋嬪簭鏉ュ疄鐜板瓨鍙栫殑涓绉嶆暟鎹簱銆傚:鏂囨湰鍨嬫暟鎹簱銆丄CCESS銆丒XCEL銆乆ML绛夌瓑 闂鍏:鏁版嵁搴撴湁鍝嚑绉 1). Sql Ser...
绛旓細Oracle鐨凷QL * Plus宸ュ叿鎻愪緵浜嗘瘮MySQL鏇村鐨勫懡浠わ紝鐢ㄤ簬鐢熸垚鎶ヨ〃杈撳嚭鍜屽彉閲忓畾涔夈4銆佸瓨鍌ㄤ笂鐨勫尯鍒細涓嶰racle鐩告瘮锛孧ySQL娌℃湁琛ㄧ┖闂达紝瑙掕壊绠＄悊锛屽揩鐓э紝鍚屼箟璇嶅拰鍖呬互鍙婅嚜鍔ㄥ瓨鍌ㄧ鐞嗐5銆佸璞″悕绉扮殑鍖哄埆锛氳櫧鐒舵煇浜涙ā寮忓璞″悕绉板湪Oracle鍜孧ySQL涓兘涓嶅尯鍒嗗ぇ灏忓啓锛屼緥濡傚垪锛屽瓨鍌ㄨ繃绋嬶紝绱㈠紩绛夈備絾鍦ㄦ煇浜涙儏鍐典笅锛屼袱涓...
绛旓細棣栧厛鏄ぇ浣撲竴鑷寸殑锛屽彧鏄垎椤垫煡璇㈡椂oracle鐢ㄧ殑浼垪锛坮ownum锛锛宮ysql鐢ㄧ殑鏄痩imit锛屽叿浣撶殑鍙互鐧惧害涓涓嬪垎椤碉紱鍙﹀oracle瀵箂ql璇彞瑕佹眰鏇翠负涓ユ牸锛岃屼笖oracle閲屽彉閲忚緝mysql鏇村鐐锛宱racle涓湁number鍨嬶紝鏈夊ぇ鏁版嵁绫诲瀷锛宮ysql娌″緱锛涘彟澶栦妇涓緥瀛愶紝oracle涓嶈兘鎻掑叆涓虹┖鍒楋紝鑰宮ysql鏄彲浠ョ殑锛堜釜浜鸿寰楋紝涓嶇煡閬撴纭笌鍚︼級...
绛旓細甯歌鐨勬暟鎹簱锛庡ACCESS锛孧SSQL锛孧YSQL锛孫RACLE锛孌B2.涓鑸墠涓変釜鐢ㄥ緱澶氫竴鐐癸紟ACC鐢ㄤ簬涓鑸殑浼佷笟缃戠珯锛庢暟鎹噺灏忥紟璁块棶閲忓皬锛庡悗涓や釜锛岀敤浜庡ぇ鐐圭殑缃戠珯锛庡湪鏁堢巼涓婁細姣擜CC瑕侀珮锛庢渶鍚庝袱涓紝涓鑸兘鏄ぇ鍨嬬殑搴旂敤骞冲彴鎵嶄細鐢ㄥ埌锛庡綋鐒讹紝杩樹細鏈夊叾瀹冪殑鏁版嵁搴擄紟浣嗕竴鑸敤寰楀皯锛...
绛旓細4.鐩稿鎶樹腑鐨勬暟鎹牎楠屾柟寮忔槸閫氳繃涓氬姟瑙掑害,鎻愬墠缂栧啓濂芥暟鍗佷釜杩斿洖缁撴灉杈冨揩鐨SQL,浠庝笟鍔¤搴︽娊鏍锋牎楠屻 鍥涖佽縼绉婚棶棰樺鐞 鏈珷鑺傚皢璁茶堪杩佺Щ杩囩▼涓鍒扮殑涓浜涢棶棰樺強鐩稿簲鐨勮В鍐虫柟寮忋 (涓)MySQL闄愬埗 鍦Oracle鍒MySQL鐨琛ㄧ粨鏋勮縼绉昏繃绋嬩腑涓昏纰板埌浠ヤ笅涓や釜闄愬埗: 1. Oracle绔殑琛ㄧ粨鏋勫洜涓烘渶鍒濊璁′笉涓ヨ皑,瀛樺湪澶ч噺鐨勫垪浣跨敤varch...
绛旓細甯歌鐨勫叧绯诲瀷鏁版嵁搴撶鐞嗙郴缁熶骇鍝佹湁Oracle銆丼QL Server銆丼ybase銆丏B2銆丄ccess绛夈 1.Oracle Oracle鏄1983骞存帹鍑虹殑涓栫晫涓婄涓涓紑鏀惧紡鍟嗗搧鍖栧叧绯诲瀷鏁版嵁搴撶鐞嗙郴缁熴傚畠閲囩敤鏍囧噯鐨凷QL缁撴瀯鍖栨煡璇㈣瑷锛鏀寔澶氱鏁版嵁绫诲瀷锛屾彁渚涢潰鍚戝璞″瓨鍌ㄧ殑鏁版嵁鏀寔锛屽叿鏈夌鍥涗唬璇█寮鍙戝伐鍏凤紝鏀寔Unix銆乄indows NT銆丱S/2銆...
