数据库为SQL SERVER,从上万条数据中,如何随机获取1000数据,并且加上查询条件;
这是我写的,可是不知道该怎么加上查询条件.忘高手赐教。
select top 10 * from baseinfo where
tel like
char(48+abs(checksum(newid())%10))+'%'+
char(48+abs(checksum(newid())%10))+'%'+
char(48+abs(checksum(newid())%10))+'%'+
char(48+abs(checksum(newid())%10))+'%'
这是我写的,可是不知道该怎么加上查询条件.忘高手赐教。
select top 10 * from baseinfo where
tel like
char(48+abs(checksum(newid())%10))+'%'+
char(48+abs(checksum(newid())%10))+'%'+
char(48+abs(checksum(newid())%10))+'%'+
char(48+abs(checksum(newid())%10))+'%'
比如
select top 10 * from baseinfo where name='张三' order by newid()
这样写会出错,如何不出错呢?
SQL Server 使用Order by newID()返回随机数据
SQLServer提供函数newid,可以随机抽取数据,举例如下
select top 100 *
from tableA
上面这条语句得到的数据是前100返回结果,一般是比较集中在某一天或者某个区域。
select top 100 *
from tableA
group by newId()
上面这条语句得到的数据数据库随即抽取的100条结果,是很好的抽样数据
use tempdb
if object_id('tb') is not null drop table tb
go
create table tb([b] varchar(50),[c] varchar(50),[d] INT)
insert into tb
select '20081201','092152',15 union all
select '20081201','092352',16 union all
select '20081201','092632',11 union all
select '20081201','096158',18 union all
select '20081201','102130',15 union all
select '20081202','092152',12 union all
select '20081202','092342',13 union all
select '20081202','093522',20 union all
select '20081202','103652',15 union all
select '20081204','092232',17 union all
select '20081204','092552',19 union all
select '20081204','095552',15
go
select top 10 * from tb where b='20081201' order by newid()
/*
b c d
-------------------------------------------------- -------------------------------------------------- -----------
20081201 092632 11
20081201 102130 15
20081201 092352 16
20081201 092152 15
20081201 096158 18(5 行受影响)
*/出什么错呢,测试OK啊
1.建立索引.
2.提高硬件配置.
3.优化查询.(不过,随机查询就不是很好的查询.)
http://topic.csdn.net/u/20080722/21/4501e9cb-a9fa-437e-aaf6-7b629bd26215.html?seed=1110065108
http://topic.csdn.net/u/20080716/11/2317d040-48e7-42da-822e-040b4c55b46d.html
WHERE 1=1