一个排序的小问题，百思不得其解

[文盲老顾]

with t as (
	select 'aa' as a
	union all select 'bb'
	union all select 'xbb'
	union all select 'fbb'
	union all select 'ebb'
	union all select 'tqbb'
	union all select 'zzbb'
	union all select 'aaabb'
	union all select 'fefbb'
),t1 as (
	select top 3 * from t order by newid()
)
select * from t1
outer apply (
	select top 2 a as sub 
	from t x
	where not exists(select top 1 1 from t1 f where f.a=x.a)
	order by newid()
) x

现在有一个小表格，里面有一些数据，想从里面随机提取出3个项目，然后，提取出的项目，随机分配2个剩余的项目，且不得重复

目前我进行到随机分配剩余项目，重复不重复且不说，为什么按照我的这个sql，剩余子项目分配完全一致？

如果要实现我的需求，应该怎么写这个指令？

[lich2005]

因为 t2 去匹配的时候，t1 的那3个随机数可能已经不是“原来的”那3个随机数了，你可以理解为 t1 的取值对于 t2 来说更像是变量，而不是静态值。 临时表就没有这样的问题了，取完值存放里面的都是静态数值。

[lich2005]

在你代码的基础小改一下。

declare @tb table(a varchar(20), b int);
--------------------------------
with t as (
	select '1aa' as a
	union all select '2bb'
	union all select '3xbb'
	union all select '4fbb'
	union all select '5ebb'
	union all select '6tqbb'
	union all select '7zzbb'
	union all select '8aaabb'
	union all select '9fefbb'
)
--------------------------------

insert into @tb(a, b)
select top 3 a,0 as lv  from t order by newid();

with t as (
	select '1aa' as a
	union all select '2bb'
	union all select '3xbb'
	union all select '4fbb'
	union all select '5ebb'
	union all select '6tqbb'
	union all select '7zzbb'
	union all select '8aaabb'
	union all select '9fefbb'
)

select *,1 as lv 
into #t2
from t
where not exists(select top 1 1 from @tb a where a.a=t.a)

select * from @tb,#t2

--drop table #t2

[文盲老顾]

with t as (
	select '1aa' as a
	union all select '2bb'
	union all select '3xbb'
	union all select '4fbb'
	union all select '5ebb'
	union all select '6tqbb'
	union all select '7zzbb'
	union all select '8aaabb'
	union all select '9fefbb'
),t1 as (
	select top 3 a,0 as lv from t order by newid()
),t2 as (
	select *,1 as lv 
	from t b
	where not exists(select top 1 1 from t1 where a=b.a)
)
select * from t1,t2

一个更简单的测试，t1表提出三个项目，t2表应该是剩余的项目，但现在t1、t2联合查询，发现t2中出现了t1的项目，这就很奇怪了

[文盲老顾]

declare @tb table(a varchar(20))
insert into @tb
select '1aa' as a
union all select '2bb'
union all select '3xbb'
union all select '4fbb'
union all select '5ebb'
union all select '6tqbb'
union all select '7zzbb'
union all select '8aaabb'
union all select '9fefbb'
select top 3 a,0 as lv into #t1 from @tb order by newid()
select *,1 as lv 
into #t2
from @tb b
where not exists(select top 1 1 from #t1 where a=b.a)
select * from #t1,#t2
drop table #t1
drop table #t2

使用临时表就没有这个问题。。。这是什么原因？