字段随机的问题。。。急

[yushu168]

我想随意一个表中，两个字段．以达到排列错乱...

SELECT TOP 10 name,
(SELECT TOP 1 student_no
FROM oa_student
ORDER BY NEWID()) AS student_no
FROM oa_student
ORDER BY NEWID()

============================
结果是:

孟涛 200516010219
王智超 200532030008
林茜 200532030008
查心园 200739010047
郭婕 200739010047
朴永哲 200739010047
张超群 200739010047
王大明 200512030112
王立孜 200532030008
寇 丽 华 200512030112


上面的结果有重复的，能不能帮我解决不出现重复的呀？

[青锋-SS]

使用distinct?

[yushu168]

蹭分的一边去啊


换个提法,假设有两个表Ａ和Ｂ,每个表都只有一个字段　Ａ．Ｆ，Ｂ．Ｆ
两个表记录数相同，现在要求一个ＳＱＬ语句把两个表合成一个新表Ｃ（Ｆ１，Ｆ２）

[dobear_0922]

参考：

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)
*/

[gahade]

这样试试

SELECT TOP 10 name,
(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()

[dawugui]

假设F字段不重复

select m.f f1 , n.f f2 from
(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

[dobear_0922]

[Quote=引用 2 楼 yushu168 的回复:]
蹭分的一边去啊


换个提法,假设有两个表Ａ和Ｂ,每个表都只有一个字段　Ａ．Ｆ，Ｂ．Ｆ
两个表记录数相同，现在要求一个ＳＱＬ语句把两个表合成一个新表Ｃ（Ｆ１，Ｆ２）
[/Quote]

你那两个字段不是在一个表中吗？
不过在两个表中也可以参照３楼的，我取的是TOP 3，，，

[hlq8210]

select distinct * from a,b

[hlq8210]

---楼主是不是要这种效果
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 3

insert #tb2 select 1 union all select 2
insert #tb2 select 1 union all select 2

alter table #tb1 
add idcol int identity(1,1)

alter table #tb2 
add idcol int identity(1,1)

select * from #tb1
select * from #tb2

select  col1,col2 from #tb1,#tb2 where  #tb1.idcol=#tb2.idcol

alter table #tb1 drop column idcol 
alter table #tb2 drop column idcol 

[wzy_love_sly]

declare @tb table (id int,name varchar(20))
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.orderid

name id
b 2
c 3
e 6

[yushu168]

dobear_0922 的思路可以．
不过好像有个小错误

insert @# select top 3 sName from @tb order by newid()
应该是insert @#（nameorno） select top 3 sName from @tb order by newid()吧