---查询出缺少的字段 然后再用TOP100 select number from tb p right join (select number from master..spt_values where type='p' and number between 1 and 100) k on p.COL=k.number where COL is null
DECLARE @NUM INT DECLARE @NUM1 INT SET @NUM1=0 SET @NUM=SELECT COUNT(*) FROM TB WHILE @NUM<=100 BEGIN INSERT TB SELECT * FROM TB SET @NUM1=@nUM1+1 ENDSELECT TOP 100 * FROM TB ORDER BY NEWID()
look:create table AA ( goodsId nvarchar(10), detail_id nvarchar(50), [count] int )insert into AA (goodsId,detail_id,[count]) values('A','001',15) insert into AA (goodsId,detail_id,[count]) values('B','002',10) insert into AA (goodsId,detail_id,[count]) values('C','003',5) insert into AA (goodsId,detail_id,[count]) values('D','004',20) --下面一起执行 declare @sql nvarchar(max) declare @count int declare @len int; declare @i int; select @sql='select * from AA union all 'select @count=count(1) from AA if(@count<100) begin select @len=100/@count+1 select @i=1 while(@i<@len) begin select @sql=@sql+'select * from AA union all ' select @i=@i+1 end select @sql=substring(@sql,1,len(@sql)-11) exec('select top 100 * from ('+@sql+') tb order by newid()') end else begin exec('select top 100 * from AA order by newid()') end
select
number
from
tb p
right join
(select number from master..spt_values where type='p' and number between 1 and 100) k
on
p.COL=k.number
where
COL is null
DECLARE @NUM1 INT
SET @NUM1=0
SET @NUM=SELECT COUNT(*) FROM TB
WHILE @NUM<=100
BEGIN
INSERT TB SELECT * FROM TB
SET @NUM1=@nUM1+1
ENDSELECT TOP 100 * FROM TB ORDER BY NEWID()
(
goodsId nvarchar(10),
detail_id nvarchar(50),
[count] int
)insert into AA (goodsId,detail_id,[count]) values('A','001',15)
insert into AA (goodsId,detail_id,[count]) values('B','002',10)
insert into AA (goodsId,detail_id,[count]) values('C','003',5)
insert into AA (goodsId,detail_id,[count]) values('D','004',20)
--下面一起执行
declare @sql nvarchar(max)
declare @count int
declare @len int;
declare @i int;
select @sql='select * from AA union all 'select @count=count(1) from AA
if(@count<100)
begin
select @len=100/@count+1 select @i=1
while(@i<@len)
begin
select @sql=@sql+'select * from AA union all '
select @i=@i+1
end
select @sql=substring(@sql,1,len(@sql)-11)
exec('select top 100 * from ('+@sql+') tb order by newid()')
end
else
begin
exec('select top 100 * from AA order by newid()')
end