select * from
(select 'A' X
union
select 'B' X
union
select 'C' X
union
select 'D' X
union
select 'E' X
union
select 'F' X
union
select 'G' X
union
select 'H' X
)
xx order by x
(select 'A' X
union
select 'B' X
union
select 'C' X
union
select 'D' X
union
select 'E' X
union
select 'F' X
union
select 'G' X
union
select 'H' X
)
xx order by x
select 'A' 结果
union select 'B'
union select 'C'
union select 'D'
union select 'E'
union select 'F'
union select 'G'
union select 'H') A
ORDER BY NEWID()
set @value=''
while @value<>'H'
begin select @value=结果 from (
select 'A' 结果
union select 'B'
union select 'C'
union select 'D'
union select 'E'
union select 'F'
union select 'G'
union select 'H') A where 结果 not in(select field1 from #tmp)
ORDER BY NEWID()insert into #tmp(field1) values(@value)
end
select * from #tmp order by id
drop table #tmp
set @value=''
while @value<>'H'
begin select @value=结果 from (
select 'A' 结果
union select 'B'
union select 'C'
union select 'D'
union select 'E'
union select 'F'
union select 'G'
union select 'H') A where 结果 not in(select filed1 from #tmp)
ORDER BY NEWID()insert into #tmp(filed1) values(@value)
end
select * from #tmp order by id
drop table #tmp
结果每次个数不一样的
id filed1
----------- ----------
1 D
2 F
3 C
4 E
5 A
6 B
7 G
8 H(8 row(s) affected)
id filed1
----------- ----------
1 F
2 E
3 H(3 row(s) affected)
----------- ----------
1 C
2 F
3 H(3 row(s) affected)id filed1
----------- ----------
1 C
2 A
3 D
4 F
5 H(5 row(s) affected)