参考:
declare @tb table(sName varchar(32), sNo varchar(16))insert @tb select 'a', '123'
union all select 'b', '234'
union all select 'c', '345'
union all select 'd', '456'
union all select 'e', '567'declare @# table(id int identity(1,1), nameORno varchar(32))insert @# select top 3 sName from @tb order by newid()
insert @# select top 3 sNo from @tb order by newid()select name=a.nameORno, no=b.nameORno
from @# a join @# b on a.id=b.id-3/*
name no
-------------------------------- --------------------------------
a 234
b 567
c 456(3 row(s) affected)
*/
declare @tb table(sName varchar(32), sNo varchar(16))insert @tb select 'a', '123'
union all select 'b', '234'
union all select 'c', '345'
union all select 'd', '456'
union all select 'e', '567'declare @# table(id int identity(1,1), nameORno varchar(32))insert @# select top 3 sName from @tb order by newid()
insert @# select top 3 sNo from @tb order by newid()select name=a.nameORno, no=b.nameORno
from @# a join @# b on a.id=b.id-3/*
name no
-------------------------------- --------------------------------
a 234
b 567
c 456(3 row(s) affected)
*/
(SELECT TOP 1 a.student_no
FROM oa_student a
WHERE a.student_no>oa_student.student_no
ORDER BY NEWID()) AS student_no
FROM oa_student
ORDER BY NEWID()
(select f,px = (select count(1) from A where f < t.f) + 1 from A t) m,
(select f,px = (select count(1) from B where f < t.f) + 1 from B t) n
where m.px = n.px如果字段F有重复,使用临时表.
select f , px = identity(int,1,1) into tmpA from A
select f , px = identity(int,1,1) into tmpB from B
select m.f f1 , n.f f2 from tmpA m , tmpB n where m.px = n.px
你那两个字段不是在一个表中吗?
不过在两个表中也可以参照3楼的,我取的是TOP 3,,,
select distinct * from a,b
---楼主是不是要这种效果
create table #tb1(col1 int)
create table #tb2 (col2 int)
insert #tb1 select 1 union all select 2
insert #tb1 select 2 union all select 3insert #tb2 select 1 union all select 2
insert #tb2 select 1 union all select 2alter table #tb1
add idcol int identity(1,1)alter table #tb2
add idcol int identity(1,1)select * from #tb1
select * from #tb2select col1,col2 from #tb1,#tb2 where #tb1.idcol=#tb2.idcolalter table #tb1 drop column idcol
alter table #tb2 drop column idcol
insert into @tb select 1,'a'
insert into @tb select 2,'b'
insert into @tb select 3,'c'
insert into @tb select 4,'d'
insert into @tb select 5,'e'
insert into @tb select 6,'f'
select a.name,b.id from
(
select row_number() over(order by name) as orderid,name from(
select top 3 name from @tb order by newid())tp1)a ,
(
select row_number() over(order by id) as orderid,id from(
select top 3 id from @tb order by newid())tp2)b
where a.orderid=b.orderidname id
b 2
c 3
e 6
不过好像有个小错误insert @# select top 3 sName from @tb order by newid()
应该是insert @#(nameorno) select top 3 sName from @tb order by newid()吧