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_Cursor1Select * from ##
drop table ##
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_Cursor1Select * from ##
drop table ##
select * from a where aid in (select top 3 aid from a b where atype=b.atype )
drop table tb
gocreate 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')
godeclare @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