select top 1 * from tb where 地址='北京' union all select top 9 * from tb order by newid()
create table #temp ( id int identity, [name] varchar(100) ) insert #temp select 'a' union all select 'b' union all select 'a' union all select 'b' union all select 'a' union all select 'c' union all select 'd' union all select 'f' union all select 'a' --SQL: --随机选择3条记录,其中name不能重复 select b.* from (select top(3) [name] from (select distinct name from #temp) a order by NEWID()) a cross apply (select top(1) * from #temp where [name] = a.name order by NEWID()) b /* id name 6 c 8 f 7 d */
newid 是能实现随机的功能吗?
select top 10 * from tb order by newid()
select top(1) * from #temp where [name] = a.name order by NEWID()这里为什么是 top(1) 而不是 top(2)
union all
select top 9 * from tb order by newid()
(
id int identity,
[name] varchar(100)
)
insert #temp
select 'a' union all
select 'b' union all
select 'a' union all
select 'b' union all
select 'a' union all
select 'c' union all
select 'd' union all
select 'f' union all
select 'a'
--SQL: --随机选择3条记录,其中name不能重复
select b.* from
(select top(3) [name] from
(select distinct name from #temp) a
order by NEWID()) a
cross apply
(select top(1) * from #temp where [name] = a.name order by NEWID()) b
/*
id name
6 c
8 f
7 d
*/
select top(1) * from #temp where [name] = a.name order by NEWID()这里为什么是 top(1) 而不是 top(2)
cross apply如果换成 outer apply功能好像是一样的。在这里貌似没区别