你的不行
select col1,col2=(select top 1 col2 from 表 where col1=a.col1 order by newid())
from TABLE1 a
group by col1
出错
select col1,col2=(select top 1 col2 from 表 where col1=a.col1 order by newid())
from TABLE1 a
结果:
col1 col2
----------- ----------
1 a
1 b
1 a
2 c
2 a
2 b
2 a
3 d
3 e
--测试数据
declare @t table(col1 int,col2 varchar(10))
insert @t select 1,'a'
union all select 1,'b'
union all select 1,'a'
union all select 2,'cc'
union all select 2,'aa'
union all select 2,'bb'
union all select 2,'aaa'
union all select 3,'ddd'
union all select 3,'eee'
--查询
select id=identity(int),* into #t
from @t
select * from #t a
where id=(select top 1 id from #t where col1=a.col1 order by newid())
drop table #t
/*--测试结果
id col1 col2
----------- ----------- ----------
1 1 a
7 2 aaa --这个明显不对
9 3 eee
--测试数据
declare @t table(col1 int,col2 varchar(10))
insert @t select 1,'a'
union all select 1,'b'
union all select 1,'a'
union all select 2,'c'
union all select 2,'a'
union all select 2,'b'
union all select 2,'a'
union all select 3,'d'
union all select 3,'e'
--查询
select id=identity(int),* into #t
from(select top 100 percent * from @t order by newid())a
select col1,col2
from #t a,(select id=min(id) from #t group by col1)b
where a.id=b.id
order by col1
--查询
select id=identity(int),* into #t
from(select top 100 percent * from 原表 order by newid())a
select col1,col2
from #t a,(select id=min(id) from #t group by col1)b
where a.id=b.id
order by col1