一道要求按比例更新数据问题的解答

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