select top 7 id=identity(int,1,1) into #id from sysobjects select top 50 PERCENT id1=id into #id1 from #id order by newid() select id2=id into #id2 from #id where id not in(select id1 from #id1) select a.id1,b.id2 from #id1 a left join #id2 b on (select count(1) from #id1 where id1<=a.id1)=(select count(1) from #id2 where id2<=b.id2) order by a.id1drop table #id1 drop table #id2 drop table #idid1 id2 ----------- ----------- 3 1 4 2 6 5 7 NULL(4 行受影响)
declare @temp table (id int) insert into @temp values(1) insert into @temp values(2) insert into @temp values(3) insert into @temp values(4) insert into @temp values(5) insert into @temp values(6) insert into @temp values(7)select identity(int,1,1) as xuhao,id into #temp from @temp order by newid()select x.id as id1,y.id as ad2 from (select xuhao,id from #temp where (xuhao%2 = 0)) x inner join (select xuhao,id from #temp where (xuhao%2 = 1))y on x.xuhao = y.xuhao - 1drop table #temp /* id1 ad2 ----------- ----------- 7 6 3 1 4 2 */
declare @t table (id int) insert into @t values(1) insert into @t values(2) insert into @t values(3) insert into @t values(4) insert into @t values(5) insert into @t values(6) insert into @t values(7)select id into #1 from @t order by newid() select identity(int,1,1) xid,id into # from #1 select a.id,b.id from # a,# b where a.id<b.id and a.xid/2 = b.xid/2 drop table #,#1
改一下,就可以了drop table #temp drop table #temp1 drop table #temp2 declare @temp table (id int) insert into @temp values(1) insert into @temp values(2) insert into @temp values(3) insert into @temp values(4) insert into @temp values(5) insert into @temp values(6) insert into @temp values(7)select identity(int,1,1) as xuhao,id into #temp from @temp order by newid() select identity(int,1,1) as xuhao,id into #temp1 from #temp where xuhao%2=0 order by newid() select identity(int,1,1) as xuhao,id into #temp2 from #temp where xuhao%2=1 order by newid() select x.id as id1,y.id as ad2 from #temp1 x,#temp2 y where x.xuhao=y.xuhao
select top 50 PERCENT id1=id into #id1 from #id order by newid()
select id2=id into #id2 from #id where id not in(select id1 from #id1)
select a.id1,b.id2 from #id1 a left join #id2 b on (select count(1) from #id1 where id1<=a.id1)=(select count(1) from #id2 where id2<=b.id2)
order by a.id1drop table #id1
drop table #id2
drop table #idid1 id2
----------- -----------
3 1
4 2
6 5
7 NULL(4 行受影响)
declare @temp table (id int)
insert into @temp values(1)
insert into @temp values(2)
insert into @temp values(3)
insert into @temp values(4)
insert into @temp values(5)
insert into @temp values(6)
insert into @temp values(7)select identity(int,1,1) as xuhao,id into #temp from @temp order by newid()select x.id as id1,y.id as ad2 from
(select xuhao,id from #temp where (xuhao%2 = 0)) x
inner join
(select xuhao,id from #temp where (xuhao%2 = 1))y on x.xuhao = y.xuhao - 1drop table #temp
/*
id1 ad2
----------- -----------
7 6
3 1
4 2
*/
insert into @t values(1)
insert into @t values(2)
insert into @t values(3)
insert into @t values(4)
insert into @t values(5)
insert into @t values(6)
insert into @t values(7)select id into #1 from @t order by newid()
select identity(int,1,1) xid,id into # from #1
select a.id,b.id from # a,# b
where a.id<b.id and a.xid/2 = b.xid/2
drop table #,#1
drop table #temp1
drop table #temp2
declare @temp table (id int)
insert into @temp values(1)
insert into @temp values(2)
insert into @temp values(3)
insert into @temp values(4)
insert into @temp values(5)
insert into @temp values(6)
insert into @temp values(7)select identity(int,1,1) as xuhao,id into #temp from @temp order by newid()
select identity(int,1,1) as xuhao,id into #temp1 from #temp where xuhao%2=0 order by newid()
select identity(int,1,1) as xuhao,id into #temp2 from #temp where xuhao%2=1 order by newid()
select x.id as id1,y.id as ad2 from #temp1 x,#temp2 y where x.xuhao=y.xuhao