日期:2014-05-17 浏览次数:20750 次
IF object_id('tempdb..#ta') is not null
DROP table #ta;
IF object_id('tempdb..#tb') is not null
DROP table #tb;
CREATE TABLE #ta ([级别] CHAR(1))
INSERT #ta ([级别])
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
CREATE TABLE #tb ([姓名] CHAR(8),[级别] CHAR(1))
INSERT #tb ([姓名])
SELECT '汪峰' UNION ALL
SELECT '林峰' UNION ALL
SELECT '马国明' UNION ALL
SELECT '房祖名'
GO
;WITH a1 AS
(
SELECT *,(ROW_NUMBER() OVER(ORDER BY [级别]))-1 re FROM #ta
)
,a2 AS
(
SELECT *,(ROW_NUMBER() OVER(ORDER BY NEWID()))%3 re FROM #tb
)
UPDATE a SET [级别]=b.[级别]
FROM a2 a
INNER JOIN a1 b ON a.re=b.re
SELECT * FROM #tb
create table #a(列1 varchar(20))
insert into #a values('a'),('b'),('c')
create table #b(姓名 varchar(20),级别 varchar(20))
insert into #b
select '汪峰',null union all
select '林峰',null union all
select '马国明',null union all
select '房祖名',null
declare @name varchar(20)
declare mycursor cursor for
select 姓名 from #b
open mycursor
fetch next from mycursor into @name
while @@FETCH_STATUS=0
begin
declare @a int,@b varchar(2)
select @a=left(rand()*10,1)
while @a>3 or @a<1
begin
select @a=left(rand()*10,1)
end
;with cte as
(
select ROW_NUMBER()over(order by 列1)id,* from #a
)
select @b=列1 from cte where id=@a
update #b set 级别=@b where 姓名=@name
fetch next from mycursor into @name
end
close mycursor
deallocate mycursor
-------------------------------------------------
姓名 级别
-------------------- --------------------
汪峰 b
林峰 c
马国明