QA要求帮忙将样例表中的数据按比例更新：
例如,要求按如下规则更新某列
50%的数据，更新为A
25%的数据，更新为B
20%的数据，更新为C
5%的数据， 更新为D
解决方案如下:
---示例数据表
DECLARE @PercentSetting TABLE
(Id INT IDENTITY PRIMARY KEY,
A INT,
Percentage NVARCHAR(50)
)
--循环插入1000条数据
DECLARE @I INT
SET @I=1
WHILE @I <= 1000
BEGIN
INSERT INTO @PercentSetting(A)
SELECT @I*100
SET @I=@I+1
END
--按比例更新-------------------
DECLARE @TempTable TABLE
(Id INT IDENTITY PRIMARY KEY,
KeyCol1 INT,
keyCol2 INT--可能需要多列主键字段
)
INSERT INTO @TempTable(KeyCol1)
SELECT
Id
FROM @PercentSetting
WHERE Percentage is null
ORDER BY NEWID()--随机排序
--先设置50%
UPDATE t
SET t.Percentage='A'
FROM @PercentSetting AS t
INNER JOIN @TempTable AS tt ON t.Id = tt.KeyCol1
WHERE tt.Id % 2 = 0
DELETE @TempTable
INSERT INTO @TempTable(KeyCol1)
SELECT
Id
FROM @PercentSetting
WHERE Percentage IS NULL
ORDER BY NEWID()--随机排序
--剩余50%的50%即为25%
UPDATE t
SET Percentage='B'
FROM @PercentSetting AS t
inner join @TempTable AS tt ON t.Id = tt.KeyCol1
WHERE tt.Id % 2 = 0
DELETE @TempTable
INSERT INTO @TempTable(KeyCol1)
SELECT
Id
FROM @PercentSetting
WHERE Percentage IS NULL
ORDER BY NEWID()--随机排序
--再剩余(25%)的1/5即为总数的5%
UPDATE t
SET Percentage = 'D'
FROM @PercentSetting AS t
INNER JOIN @TempTable AS tt ON t.Id = tt.KeyCol1
WHERE tt.Id % 5 = 0
--剩余的即为20%
UPDATE @PercentSetting
SET Percentage='C'
WHERE Percentage IS NULL
--验证结果
--验证结果
SELECT
Percentage,
COUNT(*) CountOf1000
FROM @PercentSetting
GROUP BY Percentage
Percentage CountOf1000
-------------------------------------------------- -----------
A 500
B 250
C 200
D 50