declare @amount int select @amount=count(分數) from tab --這裡改為你的表名 ;with sel as(select *,row_number() over(order by 分數 DESC) as rn from tab) update sel set 等級=case when rn<=rn*0.05 then 'S' when rn>@amount*0.05 and rn<=@amount*0.15 then 'A' when rn>@amount*0.15 and rn<=@amount*0.70 then 'B' when rn>@amount*0.70 and rn<=@amount then 'C' end
CREATE TABLE #temp (Id INT,score INT,NAME VARCHAR(10)) INSERT INTO #temp SELECT 1,round(rand()*100,0),'jack' UNION ALL SELECT 2,round(rand()*100,0),'tony' UNION ALL SELECT 3,round(rand()*100,0),'sum' UNION ALL SELECT 4,round(rand()*100,0),'luss' union all SELECT 5,round(rand()*100,0),'jack2' UNION ALL SELECT 6,round(rand()*100,0),'tony2' UNION ALL SELECT 7,round(rand()*100,0),'sum2' UNION ALL SELECT 8,round(rand()*100,0),'luss2' union all SELECT 9,round(rand()*100,0),'jack3' UNION ALL SELECT 10,round(rand()*100,0),'tony3' UNION ALL SELECT 11,round(rand()*100,0),'sum3' UNION ALL SELECT 12,round(rand()*100,0),'luss3' union all SELECT 13,round(rand()*100,0),'jack4' UNION ALL SELECT 14,round(rand()*100,0),'tony4' UNION ALL SELECT 15,round(rand()*100,0),'sum4' UNION ALL SELECT 16,round(rand()*100,0),'luss4' union all SELECT 17,round(rand()*100,0),'jack5' UNION ALL SELECT 18,round(rand()*100,0),'tony5' UNION ALL SELECT 19,round(rand()*100,0),'sum5' UNION ALL SELECT 20,round(rand()*100,0),'luss5' declare @number int --公司人数 set @number=20 ;with cte as ( select id,score,row_number()over(order by score desc) as num from #temp ) select case when num<=@number*0.05 then 'S' when num>@number*0.05 and num<=@number*0.15 then 'A' when num>@number*0.15 and num<=@number*0.70 then 'B' when num>@number*0.70 and num<=@number then 'C' end as [等级],score from cte
declare @amount int
select @amount=count(分數) from tab --這裡改為你的表名
;with sel as(select *,row_number() over(order by 分數 DESC) as rn from tab)
update sel set 等級=case when rn<=rn*0.05 then 'S'
when rn>@amount*0.05 and rn<=@amount*0.15 then 'A'
when rn>@amount*0.15 and rn<=@amount*0.70 then 'B'
when rn>@amount*0.70 and rn<=@amount then 'C' end
INSERT INTO #temp
SELECT 1,round(rand()*100,0),'jack' UNION ALL
SELECT 2,round(rand()*100,0),'tony' UNION ALL
SELECT 3,round(rand()*100,0),'sum' UNION ALL
SELECT 4,round(rand()*100,0),'luss' union all
SELECT 5,round(rand()*100,0),'jack2' UNION ALL
SELECT 6,round(rand()*100,0),'tony2' UNION ALL
SELECT 7,round(rand()*100,0),'sum2' UNION ALL
SELECT 8,round(rand()*100,0),'luss2' union all
SELECT 9,round(rand()*100,0),'jack3' UNION ALL
SELECT 10,round(rand()*100,0),'tony3' UNION ALL
SELECT 11,round(rand()*100,0),'sum3' UNION ALL
SELECT 12,round(rand()*100,0),'luss3' union all
SELECT 13,round(rand()*100,0),'jack4' UNION ALL
SELECT 14,round(rand()*100,0),'tony4' UNION ALL
SELECT 15,round(rand()*100,0),'sum4' UNION ALL
SELECT 16,round(rand()*100,0),'luss4' union all
SELECT 17,round(rand()*100,0),'jack5' UNION ALL
SELECT 18,round(rand()*100,0),'tony5' UNION ALL
SELECT 19,round(rand()*100,0),'sum5' UNION ALL
SELECT 20,round(rand()*100,0),'luss5' declare @number int --公司人数
set @number=20
;with cte as
(
select id,score,row_number()over(order by score desc) as num from #temp
)
select case when num<=@number*0.05 then 'S'
when num>@number*0.05 and num<=@number*0.15 then 'A'
when num>@number*0.15 and num<=@number*0.70 then 'B'
when num>@number*0.70 and num<=@number then 'C' end as [等级],score
from cte