A sort of small problems, a mystery-CodePudding

 
As with t (
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 the top 3 * from t order by newid () 
) 
Select * from t1 
The outer apply (
Select the top 2 a as sub 
The from t x 
Where not the exists (select top 1 1 from t1 where f f.a=x.a) 
The order by newid () 
) x

Now there is a small table, there are some data, three project want to randomly extracted from the inside, and then extract the project, randomly assigned to two of the rest of the project, and shall not repeat

At the moment, I randomly assigned the rest of the way through project, repeat don't repeat things, why in my this SQL, remaining components distribution exactly?

If you want to realize my needs, should be how to write the instructions?

CodePudding user response:

 with t as (
Select '1 aa as a 
Union all select '2 bb' 
Union all select '3 XBB' 
Union all select '4 FBB' 
Union all select '5 ebb' 
Union all select '6 TQBB' 
Union all select '7 ZZBB' 
Union all select '8 aaabb' 
Union all select '9 fefbb' 
), t1 as (
Select the top 3 a, 0 as lv from t order by newid () 
), t2 as (
Select *, 1 as lv 
The from t b 
Where not the exists (select top 1 1 from t1 where a=b.a) 
) 
Select * from t1, t2

A more simple test, the t1 table put forward three projects, t2 table should be the rest of the project, but now t1, t2 joint query, found in the t1 t2 project, it's very strange

CodePudding user response:

 declare @ TB table (a varchar (20)) 
Insert into @ TB 
Select '1 aa as a 
Union all select '2 bb' 
Union all select '3 XBB' 
Union all select '4 FBB' 
Union all select '5 ebb' 
Union all select '6 TQBB' 
Union all select '7 ZZBB' 
Union all select '8 aaabb' 
Union all select '9 fefbb' 
Select the top 3 a, 0 # as lv into t1 from @ TB order by newid () 
Select *, 1 as lv 
Into # t2 
The from @ TB b 
Where not the exists (select top 1=1 # from t1 where a b.a) 
# # the select * from t1, t2 
# drop table t1 
Drop table # t2

Using a temporary table, there is no this problem,,, what reason is this?

CodePudding user response:

Because t2 to match, the three random Numbers may have t1 is not "original" the three random number, you can understand for the t1 values for t2 is more like a variable, rather than a static value,
Temporary table, there is no such problem, take the value stored inside are static values,

CodePudding user response:

On the basis of your code enhancements,

 

Declare @ TB table (a varchar (20), b int); 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
As with t (
Select '1 aa as a 
Union all select '2 bb' 
Union all select '3 XBB' 
Union all select '4 FBB' 
Union all select '5 ebb' 
Union all select '6 TQBB' 
Union all select '7 ZZBB' 
Union all select '8 aaabb' 
Union all select '9 fefbb' 
) 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 

Insert into @ TB (a, b) 
Select the top 3 a, 0 as lv from t order by newid (); 

As with t (
Select '1 aa as a 
Union all select '2 bb' 
Union all select '3 XBB' 
Union all select '4 FBB' 
Union all select '5 ebb' 
Union all select '6 TQBB' 
Union all select '7 ZZBB' 
Union all select '8 aaabb' 
Union all select '9 fefbb' 
) 

Select *, 1 as lv 
Into # t2 
The from t 
Where not the exists (select top 1 where the from @ 1 TB a a.a=t.a) 

Select * from @ TB, # t2 

- drop table # t2