随机取数据
表a有   aid,   atype   字段.   aid为主键.
表中有几万条数据,几十种atype值
想每种atype   随机取出3条数据,怎样实现?

------解决方案--------------------
Use Pubs
Select top 0 newid() as id, * into ## from roysched

DECLARE @SBaseKID NVARCHAR(10)
DECLARE Stock_Cursor1 CURSOR FOR
Select royalty from roysched group by royalty
OPEN Stock_Cursor1
FETCH NEXT FROM Stock_Cursor1 INTO @SBaseKID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'insert into ## Select top 3 newid() AS ID,* from roysched Where royalty= ' ' '+@SBaseKID+ ' ' 'order by ID '
Print @SQL
EXEC(@SQL)
FETCH NEXT FROM Stock_Cursor1 INTO @SBaseKID
END
CLOSE Stock_Cursor1
DEALLOCATE Stock_Cursor1

Select * from ##
drop table ##
------解决方案--------------------
---try
select * from a where aid in (select top 3 aid from a b where atype=b.atype )
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(id int,name varchar(10),[count] int,time varchar(10))
insert into tb(id,name,[count],time) values(1, '刘 ', 10, '2007-04-05 ')
insert into tb(id,name,[count],time) values(2, '王 ', 11, '2007-04-05 ')
insert into tb(id,name,[count],time) values(3, '张 ', 12, '2007-04-05 ')
insert into tb(id,name,[count],time) values(4, '刘 ', 9 , '2007-04-06 ')
insert into tb(id,name,[count],time) values(5, '王 ', 14, '2007-04-06 ')
insert into tb(id,name,[count],time) values(6, '张 ', 15, '2007-04-06 ')
insert into tb(id,name,[count],time) values(7, '刘 ', 10, '2007-04-05 ')
insert into tb(id,name,[count],time) values(8, '王 ', 11, '2007-04-05 ')
insert into tb(id,name,[count],time) values(9, '张 ', 12, '2007-04-05 ')
insert into tb(id,name,[count],time) values(10, '刘 ', 9 , '2007-04-06 ')
insert into tb(id,name,[count],time) values(11, '王 ', 14, '2007-04-08 ')
insert into tb(id,name,[count],time) values(12, '张 ', 15, '2007-04-07 ')
go

declare @sql varchar(8000)
set @sql = 'select * from ( '
select @sql = @sql + 'union all select * from (select top 3 * from tb
where time = ' ' ' + time + ' ' 'order by newid()) fir '
from (select distinct time from tb) as sec
set @sql = stuff(@sql,16,9, ' ') + ' ) thir '

--print @sql
exec(@sql)

drop table tb