一个查询 查出的结果 可能是下面临时表@a 的任何几行:
(下面的name1 name2只是一个公式)
a b 可是是任何值
declare @a table(name1 varchar(5),name2 varchar(5))
insert @a
select 'a','b+2'
union all
select 'a+1','b+2'
union all
select 'a','b-1'
union all
select 'a+1','b-1'
union all
select 'a+2','b'
union all
select 'a+2','b+1'
union all
select 'a-1','b'
union all
select 'a-1','b+1' 需要得到的数据必须为:
'a','b'
'a+1','b+2'
或者是
'a','b'
'a+1','b+2'
'a','b-1'
'a+1','b-1'
或者是
'a','b'
'a+1','b+2'
'a','b-1'
'a+1','b-1'
'a-1','b'
'a-1','b+1'
'a+1','b+2'
或者是
'a','b+2'
'a+1','b+2'
'a','b-1'
'a+1','b-1'
或者是
'a','b+2'
'a+1','b+2'
'a','b-1'
'a+1','b-1'
'a-1','b'
'a-1','b+1'
insert ta
select 'a','b+2'
union all
select 'a+1','b+2'
union all
select 'a','b-1'
union all
select 'a+1','b-1'
union all
select 'a+2','b'
union all
select 'a+2','b+1'
union all
select 'a-1','b'
union all
select 'a-1','b+1'
go
declare @i int,@s varchar(1000)
select @I = rand() * 10 + 1set @s = 'select * from ta where name2 = ''b+2''
union
select top '+ltrim(@i)+' * from ta where name2 <> ''b+2'' order by name2 desc'
exec (@s)
drop table ta
create table ta(name1 varchar(5),name2 varchar(5))
insert ta
select 'a','b+2'
union all
select 'a+1','b+2'
union all
select 'a','b-1'
union all
select 'a+1','b-1'
union all
select 'a+2','b'
union all
select 'a+2','b+1'
union all
select 'a-1','b'
union all
select 'a-1','b+1'
go
declare @i int,@s varchar(1000)
select @I = rand() * 10 + 1set @s = 'select * from ta where name2 = ''b+2''
union
select top '+ltrim(@i)+' * from ta where name2 <> ''b+2'' order by name2 desc'
exec (@s)
drop table ta
cross join @a b
order by newid()