Name group grade
曹俊 1
陈元柏 1
吴智 2
朱兴望 2
黄燕萍 3
王贻芳 3
李秀荣 3
陆卫国 4要求每个行的grade列自动填充一个1~9范围的随机数(grade列值不要一样),但是group值相同的元组,其grade值相同。
曹俊 1
陈元柏 1
吴智 2
朱兴望 2
黄燕萍 3
王贻芳 3
李秀荣 3
陆卫国 4要求每个行的grade列自动填充一个1~9范围的随机数(grade列值不要一样),但是group值相同的元组,其grade值相同。
set grade =cast(rand()*10 as int)update a
set grade = (select max(grade) grade from ta b where [b.group] =[a.group])
from ta a
-- Author: liangCK 小梁
-- Date : 2008-11-20 15:46:54
---------------------------------
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (Name VARCHAR(6),[group] INT,grade INT)
INSERT INTO #T
SELECT '曹俊',1,null UNION ALL
SELECT '陈元柏',1,null UNION ALL
SELECT '吴智',2,null UNION ALL
SELECT '朱兴望',2,null UNION ALL
SELECT '黄燕萍',3,null UNION ALL
SELECT '王贻芳',3,null UNION ALL
SELECT '李秀荣',3,null UNION ALL
SELECT '陆卫国',4,null--SQL查询如下:DECLARE @t table(Name VARCHAR(6),[group] INT,grade INT)INSERT @t
SELECT *
FROM #T
ORDER BY [group]DECLARE @g int,@r intUPDATE A SET
@r=CASE WHEN [group]=@g
THEN @r
ELSE
ABS(CHECKSUM(NEWID())%(1+9-1)+1 )
END,
grade=@r,
@g=[group]
FROM @t AS AUPDATE a SET
grade=b.grade
FROM #T AS a
JOIN @t AS b
ON a.Name=b.Name
AND a.[group]=b.[group]SELECT * FROM #T/*
Name group grade
------ ----------- -----------
曹俊 1 9
陈元柏 1 9
吴智 2 7
朱兴望 2 7
黄燕萍 3 6
王贻芳 3 6
李秀荣 3 6
陆卫国 4 3(8 行受影响)
*/