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，剩余子專案分配完全一致？
如果要實作我的需求，應該怎么寫這個指令？
uj5u.com熱心網友回復：
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的專案，這就很奇怪了
uj5u.com熱心網友回復：
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
使用臨時表就沒有這個問題。。。這是什么原因？
uj5u.com熱心網友回復：
因為 t2 去匹配的時候，t1 的那3個亂數可能已經不是“原來的”那3個亂數了，你可以理解為 t1 的取值對于 t2 來說更像是變數，而不是靜態值。臨時表就沒有這樣的問題了，取完值存放里面的都是靜態數值。
uj5u.com熱心網友回復：
在你代碼的基礎小改一下。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/183416.html
標籤：疑難問題
上一篇：對復合索引前邊的列模糊查詢顯示key_len長度問題
下一篇：一起來學PS