| 如何随机取记录?(MSSQL、Access、MySql) |
1、数据量少的时候可以使用sql自带的newid()函数
h1a9H3 http://www.numino.net
select top 7 from table order by newid()
5iKYS2 http://www.numino.net
select top 5 a.columName from (select distinct columName from tableName) a order by newid()
W94Z00 http://www.numino.net
2、数据量大则不适合用上述方法了,简单算法:
6s08MX http://www.numino.net
统计表里所有的数据 N，然后返回一个小于N的随机数，再选择一条ID大于N就行了！
vulLUQ http://www.numino.net
随机提取10条记录的例子：
O16Xo8 http://www.numino.net
Sql server：
rvZ5c2 http://www.numino.net
select top 10 * from 表 order by newid()
C8ZbZH http://www.numino.net
Access：
GX9chz http://www.numino.net
SELECT top 10 * FROM 表 ORDER BY Rnd(id)
0cyWsX http://www.numino.net
Rnd(id) 其中的id是自动编号字段，可以利用其他任何数值来完成
x3TfJj http://www.numino.net
比如用姓名字段(UserName)
1oS0Hh http://www.numino.net
SELECT top 10 * FROM 表 ORDER BY Rnd(len(UserName))
J0Leds http://www.numino.net
MySql：
xpCwpl http://www.numino.net
Select * From 表 Order By rand() Limit 10
atcDgA http://www.numino.net
==========================================================
asr3f1 http://www.numino.net
ACCESS可以用RND函数结合ASP中的RND函数来实现随机抽取。
RQ0J6Y http://www.numino.net
<%
d3p07V http://www.numino.net
...
PnYtO1 http://www.numino.net
Randomize
hkr0ZI http://www.numino.net
strSQL = "SELECT TOP 1 字段列表 FROM 表名 ORDER BY RND(-(编号字段)*" & RND() & ")"
2wj8GJ http://www.numino.net
...
e3DFTK http://www.numino.net
%>
54VysL http://www.numino.net
===============================================
RCruH8 http://www.numino.net
要产生指定范围的随机整数，请使用以下公式：
h4PSPd http://www.numino.net
Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
4xQs3Z http://www.numino.net
这里， upperbound 是此范围的上界，而 lowerbound 是此范围内的下界
IwiKtw http://www.numino.net
==========================================================
oqrBsr http://www.numino.net
随机提取10条记录的例子：
dNxVPT http://www.numino.net
Sql server：
Jvu8Kf http://www.numino.net
select top 10 * from 表 order by newid()
H69dn4 http://www.numino.net
Access：
5zC6PY http://www.numino.net
SELECT top 10 * FROM 表 ORDER BY Rnd(id)
gp65Zv http://www.numino.net
Rnd(id) 其中的id是自动编号字段，可以利用其他任何数值来完成
y4rC8T http://www.numino.net
比如用姓名字段(UserName)
U31bRJ http://www.numino.net
SELECT top 10 * FROM 表 ORDER BY Rnd(len(UserName))
nVh6y2 http://www.numino.net
MySql：
2WS7GH http://www.numino.net
Select * From 表 Order By rand() Limit 10
pAp99o http://www.numino.net
Lzqg3D http://www.numino.net
783ARn http://www.numino.net
========================================================
D7TU9O http://www.numino.net
select top 4 * from DataTable order by rnd(-(id+rnd()))
Vr97t3 http://www.numino.net
6W9aLH http://www.numino.net
=====================================================
25q3pT http://www.numino.net
ASP&Access随机读取某段数据
l51Ao7 http://www.numino.net
在网上也搜索了很多,分享下我的方法...
fc9Vq0 http://www.numino.net
以下是源代码：
QKeHAt http://www.numino.net
Randomize(Timer())
XfG1Gc http://www.numino.net
SQL = "SELECT TOP 100 ID FROM table1 ORDER BY Rnd(-(ID + " & Int((10000 * Rnd) + 1) & "))"
Vnerc6 http://www.numino.net
Set RS = Server.CreateObject("ADODB.RecordSet")
pQqHfw http://www.numino.net
RS.Open SQL,Conn,1,1
0UPHKP http://www.numino.net
If Not RS.EOF Then
MZchLZ http://www.numino.net
RS.MoveFirst
8qKx37 http://www.numino.net
For I = 1 To RS.RecordCount
uSpad0 http://www.numino.net
Response.Write RS(0) & "<br>"
hif1V2 http://www.numino.net
RS.MoveNext
162V8F http://www.numino.net
Next
5zyD9H http://www.numino.net
End If
Hb740M http://www.numino.net
RS.Close
Wj8ZgB http://www.numino.net
Set RS = Nothing
|
|