关于数据库里随机的有关问题，给高分

关于数据库里随机的问题，急，给高分
表一
id name
1 张三
2 李四
3 王二麻子
4 赵九
 


表二

id nameid
1 1,3,4 
2 2,3,4 
3 4,8,2


从表一中随机抽出三条记录的id，按顺序插入表二中,表二本来就有数据
是update 到nameid这个字段中

[解决办法]
拆分再合并

随机用order by NEWID()
[解决办法]
 

SQL code

SELECT TOP 3 * INTO #TEMP  FROM TABLE1INSERT ITNO  TABLE1SELECT  * FROM  #TEMP UPDATE  table2  set   nameid=name from #temp  a join daoru b on  a.id=b.id
[解决办法]
SQL code
/****************************************************************************************************************************************************** 合并分拆表数据 整理人：中国风(Roy) 日期:2008.06.06 ******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null     drop table Tab Go Create table Tab([Col1] int,[Col2] nvarchar(1)) Insert Tab select 1,N'a' union all select 1,N'b' union all select 1,N'c' union all select 2,N'd' union all select 2,N'e' union all select 3,N'f' Go 合并表: SQL2000用函数: go if object_id('F_Str') is not null     drop function F_Str go create function F_Str(@Col1 int) returns nvarchar(100) as begin     declare @S nvarchar(100)     select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1     return @S end go Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: 方法1: select     a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') from     (select distinct COl1 from Tab) a Cross apply     (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 方法2: select     a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) from     (select distinct COl1 from Tab) a cross apply     (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)                 .query(' <Tab>                 {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")}                 {concat("",string(/Tab[last()]/@COl2))}                 </Tab>')                 )b SQL05用CTE: ;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) ,Roy2 as (select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 union all select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 生成结果: /* Col1        COl2 ----------- ------------ 1          a,b,c 2          d,e 3          f (3 行受影响) */ --参考拆分表:--> --> (Roy)生成測試數據 if not object_id('Tab') is null    drop table TabGoCreate table Tab([Col1] int,[COl2] nvarchar(5))Insert Tabselect 1,N'a,b,c' union allselect 2,N'd,e' union allselect 3,N'f'Go--SQL2000用辅助表:if object_id('Tempdb..#Num') is not null    drop table #Numgoselect top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns bSelect     a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from     Tab a,#Num bwhere    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','--2000不使用辅助表Select    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) from     Tab a join master..spt_values  b     ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)where     substring(','+a.COl2,b.number,1)=','SQL2005用Xml:select     a.COl1,b.Col2from     (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)aouter apply    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))bSQL05用CTE:;with roy as (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tabunion allselect Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>'')select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:/*Col1        COl2----------- -----1           a1           b1           c2           d2           e3           f*/ 
 
[解决办法]
SQL code
create table tb(id int,name nvarchar(10))insert into tb select 1,'张三'insert into tb select 2,'李四'insert into tb select 3,'王二麻子'insert into tb select 4,'赵九'insert into tb select 5,'王3麻子'insert into tb select 6,'王4麻子'insert into tb select 7,'王5麻子'insert into tb select 8,'王6麻子'insert into tb select 9,'王7麻子'create table t2(id int,nameid nvarchar(10))insert into t2 select 1,'1,3,4'  insert into t2 select 2,'2,3,4'  insert into t2 select 3,'4,8,2'godeclare @i intset @i=1while exists(select 1 from t2 where id>=@i)begin    update t2 set nameid=(    select stuff((select ','+LTRIM(id) from(    select top 3 id from tb order by newid()    )t for xml path('')),1,1,'')    ) where id=@i    set @i=@i+1endgoselect * from t2/*id          nameid----------- ----------1           9,5,12           6,9,13           2,3,8(3 行受影响)*/godrop table tb,t2
[解决办法]
SQL code
declare @str varchar(20)udpate 表2 set nameid=@str,           @str=stuff((select ','+rtrim(id) from 表1 order by newid() for xml path('')),1,1,'')